I have been trying to find a solution to generate a CSV document from MySQL using PHP. To generate the CSV from MySQL was quite simple, but getting the document to open the file in both Internet Explorer and Firefox took a bit of time. Looking at examples on the internet, they either worked in Firefox but not in Internet Explorer, or the other way around.

Here is my implementation of MySQL CSV exporting in PHP. You can simply replace the $sql variable with an SQL statement of your choice.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

<?php
// by Andrew Cetinick
// jumbabox.com 2008
 
// CONFIG //
$host="localhost";
$database="database";
$username="username";
$password="password";
// Must be a writeable location for file
$output_file="export.csv";
 
// The query to output to CSV
$sql = "select * from tbl1";
// Support for multi-table select
// $sql = "SELECT * FROM tbl2, tbl1 WHERE tbl1.col1 = tbl2.col2";
 
//---------------------------------------------------
 
// Connect database
mysql_connect($host,$username,$password);
mysql_select_db($database);
 
$result=mysql_query($sql);
 
$output = '';
 
// Get a list of all the fields in the table
// $fields = mysql_list_fields($database,$table);
// Count the number of fields
$count_fields = mysql_num_fields($result);
 
// Put the name of all fields to $out.
for ($i = 0; $i < $count_fields; $i++) 
{
	$field=mysql_fetch_field($result);
	$output.= '"'.$field->name.'",';
}
$output .="\n";
 
// Add all values in the table to $out.
while ($row = mysql_fetch_array($result)) 
{
	for ($i = 0; $i < $count_fields; $i++) 
	{
		$output .='"'.$row["$i"].'",';
	}
	$output .="\n";
}
 
// Output the file to the local filesystem.  You could append a 
// date to the filename to keep a record of the exports.
 
// Open a new output file
$file = fopen ($output_file,'w');
// Put contents of $output into the $file
fputs($file, $output);
fclose($file);
 
// This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");
// Internet Explorer support 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename=report.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $output;
?>