Php export data from mysql to excel

Php export data from mysql to excel : We sometimes need a script in php which can export the mysql table’s data into csv, excel or in some other format. Here in this tutorial we are going to create a script which will export the table’s data into excel file. In this tutorial we will go step by step with example and demo. You can download the complete script free.

Php export data from mysql to excel

Here are steps to export data from mysql to excel using PHP Script.


First specify the table from which you want to export data in excel. Here we have created a script to create table in mysql. First of all please run the below script to create the table and insert the sample data so that we can proceed for php code which will pull data from this table and export it into the excel file.

Create My-Sql Table:

  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)

-- Dumping data for table `test_user`

INSERT INTO `test_user` (`id`, `name`, `email`, `phone`) VALUES
(1, 'John', '', '121323232'),
(2, 'Kelly', '', '121212122'),
(3, 'Ryan', '', '22222212'),
(4, 'Kojo', '', '12144444'),
(5, 'Kinjal', '', '22555212'),
(6, 'Jonny', '', '121334444'),
(7, 'Barak', '', '2444444512'),
(8, 'Uman', '', '12334444');

After running the above query in mysql you will have a table named test_user with above sample data.

Export data in excel from mysql using php

Php Script To Export Data from Mysql Table to Excel File

Here we have created simple script to export data from mysql table to excel file-

Php Script To Export Data from Mysql Table to Excel File & Download:

header("Content-type: application/vnd-ms-excel");
$fileName = "testUsers";
header("Content-Disposition: attachment; filename=".$fileName.".xls");
<table border="1">
	//Mysql connection
	mysql_connect("localhost", "root", "");
	//query get data
	$sql = mysql_query("SELECT * FROM test_user ORDER BY id ASC Limit 0, 7");
	$no = 1;
	while($data = mysql_fetch_assoc($sql)){
		echo '

Try it & Download »

Headers header(“Content-type: application/vnd-ms-excel”); & header(“Content-Disposition: attachment; filename=”.$fileName.”.xls”); are used to download convert the plain data in excel format and save it.

You can try and download the full code from the above link provided along with the try it button.


Add Comment