PHP - Export Mysql Data To Excel Example

PHP 8 , PHP

Nicesnippets

137

11-05-2021


Hi Guys,

In this blog, i will share with you how to export mysql data to excel using php. we will see eport mysql data into excel file in php. I am going to learn you php exporting data from php to excel.

Here I will create one simple PHP page that will display data from the My SQL database in tabular format and then we put one button. By clicking this button, we can export the Excel file which is automatically downloaded to your machine.

Here i will give you full example for export mysql data to excel in php So let's follow bellow step by step.

Create Table


Run following command to create users table.

CREATE TABLE `users` (

`id` int(11) AUTO_INCREMENT PRIMARY KEY,

`name` varchar(100) NOT NULL

) ENGINE=InnoDB;

Successfully users table create after insert some dummy data for show data and export to excel data So let's run following sql command to insert some data:

INSERT INTO `users` (`name`) VALUES

('Tom'),

('Starc”)

('Walton'),

('watson');

index.php

<!DOCTYPE html>

<html>

<head>

<title></title>

</head>

<body>

<?php

$conn = mysqli_connect("localhost","root","","blog");

$query = "SELECT * FROM users";

$result = mysqli_query($conn,$query);

?>

<table border="2px solid black">

<tr>

<th>Id</th>

<th>Name</th>

</tr>

<?php while ($row = mysqli_fetch_assoc($result)) { ?>

<tr>

<td><?php echo $row['id']; ?></td>

<td><?php echo $row['name']; ?></td>

</tr>

<?php } ?>

</table>

<br>

<form action="excel.php" method="post">

<input type="submit" name="export" value="Export To Excel">

</form>

</body>

</html>

excel.php

<!DOCTYPE html>

<html>

<head>

<title></title>

</head>

<body>

<?php

$conn = mysqli_connect("localhost","root","","blog");

$query = "SELECT * FROM users";

$result = mysqli_query($conn,$query);

?>

<table border="2px solid black">

<tr>

<th>Id</th>

<th>Name</th>

</tr>

<?php while ($row = mysqli_fetch_assoc($result)) { ?>

<tr>

<td><?php echo $row['id']; ?></td>

<td><?php echo $row['name']; ?></td>

</tr>

<?php } ?>

</table>

<?php

if ($query == true) {

header("Content-Type:application/xls");

header("Content-Disposition:attachment, filename=download.xls");

}

?>

</body>

</html>

It will help you....


Recommended Posts