Export Data to Excel with PHP and MySQL

In our previous tutorial, we have explained how to Import CSV File into MySQL using PHP. In this tutorial, we will explain How To 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.

Also, read:

• Export jQuery Datatable Data To PDF, Excel, CSV & Copy with PHP
• Import CSV File into MySQL using PHP

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 may also like:

• Build Live Chat System with Ajax, PHP & MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Invoice System with PHP & MySQL
• Push Notification System with PHP & MySQL
• Create Bootstrap Cards with PHP and MySQL
• Build Content Management System with PHP & MySQL
• Convert Unix Timestamp To Readable Date Time in PHP
• Ajax Drop Down Selection Data Load with PHP & MySQL
• Inventory Management System with Ajax, PHP & MySQL
• Drag and Drop File Upload using jQuery and PHP
• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL

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