Snippets

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;
	exit;
}

Source: http://snipplr.com/view/2234/export-mysql-query-results-to-c...

Share this snippet

 

Comments (2) - Leave yours

  1. Will Anderson said:

    This sample makes the assumption that none of the field values will contain a double quote (“). To address this, replace:

    $l["$i"]

    with:

    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.

  2. Impec said:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please respect the following rules: No advertising, no spam, no keyword in name field. Thank you!