February 7, 2016

Php export data from mysql to excel

❮❮ Previous

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.

Table

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:

CREATE TABLE IF NOT EXISTS `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8;

--
-- Dumping data for table `test_user`
--

INSERT INTO `test_user` (`id`, `name`, `email`, `phone`) VALUES
(1, 'John', 'johnk@yopemail.com', '121323232'),
(2, 'Kelly', 'kellyk@yopemail.com', '121212122'),
(3, 'Ryan', 'ryank@yopemail.com', '22222212'),
(4, 'Kojo', 'kojao@yopemail.com', '12144444'),
(5, 'Kinjal', 'kinjal@yopemail.com', '22555212'),
(6, 'Jonny', 'jonny@yopemail.com', '121334444'),
(7, 'Barak', 'barak@yopemail.com', '2444444512'),
(8, 'Uman', 'uman@yopemail.com', '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:

<?php
header("Content-type: application/vnd-ms-excel");
$fileName = "testUsers";
header("Content-Disposition: attachment; filename=".$fileName.".xls");
?>
<table border="1">
    <tr>
    	<th>NO.</th>
		<th>NAME</th>
		<th>EMAIL</th>
		<th>PHONE</th>
	</tr>
	<?php
	//Mysql connection
	mysql_connect("localhost", "root", "");
	mysql_select_db("myDb");
	
	//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 '
		<tr>
			<td>'.$no.'</td>
			<td>'.$data['name'].'</td>
			<td>'.$data['email'].'</td>
			<td>'.$data['phone'].'</td>
		</tr>
		';
		$no++;
	}
	?>
</table>

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.


Advertisements

Add Comment

❮❮ Previous
,