technology made simple.

Export data from MySQL to CSV using PHP

March 20th, 2008 Posted in webdev

In a recent project, we were collecting registrations for an event using a PHP web form and a MySQL database. The office worker wanted to download all of the collected data in an excel file. In a subsequent meeting with another office worker, the idea of easily downloading the collected data into excel was very convenient for performing mail merges and other administrative tasks, so I tried to make one “csvdump.php” file which met many needs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php
$tbl=$_GET['tbl']; //get the table to export from the parameter in the URL
$cur=date("m-j-Y"); //construct today's date 
$filename="basename_".$cur.".csv"; //construct the default filename 
header("Content-Type: text/csv");
header("Content-Disposition: inline; filename=$filename");
include("connect.php");
$query="SELECT * FROM $tbl;";
$result=mysql_query($query) or die(mysql_error());
//loop through field names and put them in the first row, separated by commas
while($field = mysql_fetch_field($result))
{ 
	print("$field->name,");
	$fields[]=$field->name;
}
print("\r\n"); //put a line break after the first row
//loop through records, separating them with commas
while($row = mysql_fetch_array($result)) 
{ 
                foreach($fields as $field)
                {
                                print("$row[$field],");
                }
                print("\r\n"); //line break after each row
} 
?>

Post a Comment