Skip to main content

Exporting Data to Excel using PHP & MySQL

Export Data to Excel is a common feature of web applications to allow dynamic data export to Excel file to save locally for further use. If you’re a PHP developer and thinking to implement data export to excel, then you’re here at right place. You will learn here how to implement data export to excel using PHP & MySQL.

In our previous tutorial you have learned how to export data to excel in CodeIgniter, now in this tutorial we will explain how to export data to excel with PHP and MySQL.

We will cover this tutorial step by step to create live demo to implement data export to excel with PHP and MySQL. You can also download complete source code of live.

Also, read:

So let’s start implementing data export to excel with PHP and MySQL. Before we begin, take a look on files structure for this example.

  • index.php:
  • export.php:

Step1: Create MySQL Database Table

As we will cover this tutorial with example to export data to Excel file, so first we will create MySQL database table developers to store developer records.

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;

We will also insert few records to this table.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');

Step2: Get Records from MySQL Database Table

In export.php file, we will get developer records from MySQL database table developers and store into an array.

include_once("db_connect.php");
$sqlQuery = "SELECT name, gender, age, skills, address, designation FROM developers LIMIT 10";
$resultSet = mysqli_query($conn, $sqlQuery) or die("database error:". mysqli_error($conn));
$developersData = array();
while( $developer = mysqli_fetch_assoc($resultSet) ) {
	$developersData[] = $developer;
}	

Step3: Display Records with Export to Excel Button

In index.php file, we will display developer records from $developersData array. We will also add data export button to export data.

<div class="container">		
	<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="dataExport" name="dataExport" 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>Skills</th>			
			<th>Address</th>
			<th>Designation</th>
		</tr>
		<tbody>
			<?php foreach($developersData 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 ['skills']; ?></td>			   
			   <td><?php echo $developer ['address']; ?></td>
			   <td><?php echo $developer ['designation']; ?></td>   
			   </tr>
			<?php } ?>
		</tbody>
    </table>		
</div>

Step4: Implement Data Export to Excel

Now we will implement export data to excel when export button clicked. We will use $developersData array for data exported and saved into an xlsx file.

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

You may also like:

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

3 thoughts on “Exporting Data to Excel using PHP & MySQL

  1. Hello, first of all, good job and nice article,

    I have just one little problem, if you can help me, thanks!
    i look the tutorial (export from php mysql to excel) and adapte the code to my database table, and works fine, but when i put “where id_form =’”.$id_form.”‘”,
    the select from works fine, but the export, give me a white and clear excel file, i try put the “id_form” in the index field but, still not working.

    Any solution ? I dont change nothing in the code, just put the database connection for my website (not localhost), insert more data to export, and working, and the condition “where id = id” because, in the backoffice of the website i’m building, the client will select the content he wants, and export just that content.

  2. hello

    Nice article.
    In my case, the output of the SQL query which changes everytime by making few selections is displayed on the page- how can I download the result as excel?

Comments are closed.