Export Data to Excel with PHP and MySQL

Previously we have published tutorial to Import CSV Data to MySQL with PHP and get huge response from our readers. Many of our users requested to also publish tutorial to export data to excel in PHP. Exporting data in Excel format is very useful feature to allow users to save data for offline use as the Excel format is the best format to store data in a file. So in this tutorial you will learn how to export data to excel with PHP and MySQL. The tutorial explained in easy steps with live demo and link to download source code.

As we have covered this tutorial with live demo to export data to excel file with PHP and MySQL, so the file structure for this example is following.

• index.php


• export_data.php

Steps1: Create MySQL Database Table
First we will create MySQL database table developer using below query to display developers records and export data into excel file.
CREATE TABLE `developers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`skills` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



Steps2: Get MySQL Data
We will get records from MySQL database table developers and store into an array to display records and export records into excel file.
include_once("db_connect.php");
$sql_query = "SELECT name, gender, address, designation, age FROM developers LIMIT 10";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
$developer_records = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
$developer_records[] = $rows;
}


Steps3: Create HTML and Display Records with Export Button
In index.php, we will create HTML and display records using records array. We will also create Form with export button to export data on form submit.
<div class="container">
<h2>Export Data to Excel with PHP and MySQL</h2>
<div class="well-sm col-sm-12">
<div class="btn-group pull-right">
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
<button type="submit" id="export_data" name='export_data' value="Export to excel" class="btn btn-info">Export to excel</button>
</form>
</div>
</div>
<table id="" class="table table-striped table-bordered">
<tr>
<th>Name</th>
<th>Gender</th>
<th>Age</th>
<th>Designation</th>
<th>Address</th>
</tr>
<tbody>
<?php foreach($developer_records as $developer) { ?>
<tr>
<td><?php echo $developer ['name']; ?></td>
<td><?php echo $developer ['gender']; ?></td>
<td><?php echo $developer ['age']; ?></td>
<td><?php echo $developer ['designation']; ?></td>
<td><?php echo $developer ['address']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>


Steps4: Export Data to Excel
Now finally in export_data.php we will implement functionality to export data to excel with php. We will check for FORM submit POST value to export data.
if(isset($_POST["export_data"])) {
$filename = "phpzag_data_export_".date('Ymd') . ".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
$show_coloumn = false;
if(!empty($developer_records)) {
foreach($developer_records as $record) {
if(!$show_coloumn) {
// display field/column names in first row
echo implode("\t", array_keys($record)) . "\n";
$show_coloumn = true;
}
echo implode("\t", array_values($record)) . "\n";
}
}
exit;
}


You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]