Data Export to Excel with PHP and MySQL

In our previous tutorial, we have explained how to Create Ajax Pagination with PHP and MySQL. In this tutorial, we will explain how to implement Data Export to Excel file using PHP and MySQL.

Data export is a feature of web applications to allows users to save data list in a file to personal computer to use in future. We can export data in different file format but Excel is the best format as its supported by wide range of applications.

We have handled this tutorial in easy steps with live demo and link to download demo source code.

Also, read:

Filter Search Result with Ajax, PHP & MySQL

So let’s create example to export MySQL data to Excel using PHP and MySQL. The file structure for the example is following.

index.php
data.php
export.php

Steps1: Create MySQL Database Table

As we will disp0lay records and then export that records to excel file, so will create MySQL database table employee using below query.

CREATE TABLE `employee` (
  `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 records from MyQL database and store in an array

In data.php file, we will get employee records from MySQL database table and store records in an array to display records and then export data into excel file.

<?php
$sql_query = "SELECT name, gender, address, designation, age FROM employee LIMIT 3";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
$data_records = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
	$data_records[] = $rows;
}
?>

Steps3: Display data records with export button

In index.php file, we will create HTML to display employee records using data array. We will also create Form with export button to export data on button click.

<div class="container">	
	<h2>How to Export Data to Excel in PHP</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" name='export' 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($data_records as $record) { ?>
			   <tr>
			   <td><?php echo $record ['name']; ?></td>
			   <td><?php echo $record ['gender']; ?></td>
			   <td><?php echo $record ['age']; ?></td>   
			   <td><?php echo $record ['designation']; ?></td>
			   <td><?php echo $record ['address']; ?></td>   
			   </tr>
			<?php } ?>
		</tbody>
    </table>	
</div>

Steps4: Implement export data to excel with PHP

Now in export.php, we will implement functionality to export data into excel with PHP using data array. The export data functionality wioll be handled on Form submit with export button pressed.

<?php
if(isset($_POST["export"])) {	
	$filename = "coderszine_export_".date('Ymd') . ".xls";			
	header("Content-Type: application/vnd.ms-excel");
	header("Content-Disposition: attachment; filename=\"$filename\"");	
	$is_coloumn = false;
	if(!empty($data_records)) {
	  foreach($data_records as $value) {
		if(!$is_coloumn) {		 
		  echo implode("\t", array_keys($value)) . "\n";
		  $is_coloumn = true;
		}
		echo implode("\t", array_values($value)) . "\n";
	  }
	}
	exit;  
}
?>

You may also like:

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download