Export MySQL query results to CSV

This will create a .csv file with your MySQL query results.

// Export to CSV
if($_GET['action'] == 'export') {
	$rsSearchResults = mysql_query($sql, $db) or die(mysql_error());
	$out = '';
	$fields = mysql_list_fields('database','table',$db);
	$columns = mysql_num_fields($fields);
	// Put the name of all fields
	for ($i = 0; $i < $columns; $i++) {
	$l=mysql_field_name($fields, $i);
	$out .= '"'.$l.'",';
	$out .="n";
	// Add all values in the table
	while ($l = mysql_fetch_array($rsSearchResults)) {
	for ($i = 0; $i < $columns; $i++) {
	$out .='"'.$l["$i"].'",';
	$out .="n";
	// Output to browser with appropriate mime type, you choose ;)
	header("Content-type: text/x-csv");
	//header("Content-type: text/csv");
	//header("Content-type: application/csv");
	header("Content-Disposition: attachment; filename=search_results.csv");
	echo $out;
  • This sample makes the assumption that none of the field values will contain a double quote (“). To address this, replace:



    str_replace( '"', '""', $l["$i"])

    This assumes you’re escaping a single double quote with two double quotes (“”), but that’s pretty standard for CSV files.

    Also, when you copy/pasted from the source site, you lost the backslash (\) for the newlines.

  • If you will need to use data in Excel it will be better to export them in xml format. You can format the columns, assign the width of them and do other thing to make the data convenient to use in Excel. To know the format of XML file that can be imported in Excel you can create sample document in it. Then export it in XML format and open in Notepad. Its structure quite simple to write code in PHP to export data into necessary format.