PHP: Export Query Results to a CSV File

Printing out nicely formated tables in HTML is good, but the downside is that sorting and aggregation options can be sometimes limited. If you didn’t write the code for it, your users won’t be able to do it. This is why a lot of business people like Excel – it gives them the power to sort, aggregate and transform data without having to talk to the developers and wait till a given data filter or sorting feature is implemented, tested and rolled out into production. So I usually include simple export-to-excel option in a lot of the reports. Here is the little generic function I use to dump out the data in a comma separated values (CSV) format, and set the headers so that the browser prompts the user to save it or open it in Excel.

<?php
// (c) 2007 Lukasz Grzegorz Maciak
// Code Snippet ID: 5e8cf864-db67-4a30-9857-2ce8f3fcb1d5
 
// takes a database resource returned by a query
function csv_from_mysql_resource($resource)
{
	$output = "";
	$headers_printed = false;
 
	while($row = mysql_fetch_array($resource, MYSQL_ASSOC))
	{
		// print out column names as the first row
		if(!$headers_printed)
		{
			$output .= join(',', array_keys($row)) ."\n";
			$headers_printed = true;
		}
 
		// remove newlines from all the fields and
		// surround them with quote marks
		foreach ($row as &$value)
		{
			$value = str_replace("\r\n", "", $value);
			$value = "\"" . $value . "\"";
		}
 
		$output .= join(',', $row)."\n";
 
	}
 
	// set the headers
	$size_in_bytes = strlen($output);
	header("Content-type: application/vnd.ms-excel");
	header("Content-disposition:  attachment; filename=export_data.csv; size=$size_in_bytes");
 
	// send output
	print $output;
	exit;
}
 
?>

Note that you will be sending headers to the browser, so you need to run this function before you print any HTML on the page. I’m removing newlines from all the text fields – since my table had 2 TEXT columns with free form textual data in them, the newlines were causing no end of havoc. Note that the & operator doesn’t work in PHP4 so you might need to do some old fashioned assignment.

This entry was posted in programming and tagged , . Bookmark the permalink.



21 Responses to PHP: Export Query Results to a CSV File

  1. Anthony V UNITED STATES Mozilla Firefox Windows says:

    this is a livesaver! thank you!

    Whoa, so happy I didn’t have to code this! :)

    Reply  |  Quote
  2. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    Awesome. I’m glad you found this useful. :)

    Reply  |  Quote
  3. Barbara F UNITED STATES Internet Explorer Windows says:

    This is EXACTLY what I was looking for – and so simple to use.

    Thank you!!!!!

    Reply  |  Quote
  4. Joe UNITED STATES Mozilla Firefox Windows says:

    Instead of removing the ‘/r/n’ you should consider replacing it with ” to preserve the line break position without causing havoc in your CSV.

    You can always convert back from ” to ‘\r\n’ if you need to later but you cannot add newlines back in if you removed them…

    Reply  |  Quote
  5. Joe UNITED STATES Mozilla Firefox Windows says:

    replace it with an HTML Line break:

    the ‘s got eaten out of my last comment.

    Reply  |  Quote
  6. Joe UNITED STATES Mozilla Firefox Windows says:

    I meant: ”

    Reply  |  Quote
  7. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    WordPress strips HTML tags by default. If you use the code button you can override that behavior. Either that, or use HTML entities &lt; and &gt; for < and >

    Thanks for the tip though :)

    Reply  |  Quote
  8. Chris UNITED KINGDOM Internet Explorer Windows says:

    I’m a PHP newbie and don’t understand how you get this function to execute and pass it a query it’ll output in CSV format.

    I either get the error message “Parse error: parse error, unexpected ‘&’, expecting T_VARIABLE or ‘$’” in the line where “foreach ($row as &$value)
    ” appears – I’m running PHP5 – or else a blank white page. Any chance you can expand on implementing this function?

    Reply  |  Quote
  9. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    You want to run your query in the usual way and then you pass the result to the function:

    <?php
    // lets say my function is in a file cvs_export.php
    require("cvs_export.php")
     
    // connect to the db
    $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');
    mysql_select_db('my_database');
     
    // run the query
    $result = mysql_query("SELECT * FROM my_table");
     
    // pass the result to my function:
    csv_from_mysql_resource($result);
    ?>

    As for the error, it almost looks like something you would get with PHP4 which has no passing by reference. You can easily rewrite the foreach loop like this:

    foreach ($row as $key => $value)
    {
            $row[$key] = str_replace("\r\n", "", $value);
            $row[$key] = "\"" . $value . "\"";
    }

    This removes the pass by reference syntax that seems to be causing the error. Just make sure you don’t have any white spaces before or after the PHP tags and you should be fine. :)

    Reply  |  Quote
  10. Chris UNITED KINGDOM Mozilla Firefox Windows says:

    Thanks Luke, that worked great and really helps with my learning. I will be sure to explore more of your PHP/MySQL posts.
    Chris

    Reply  |  Quote
  11. Adam UNITED STATES Mozilla Firefox Windows says:

    Thanks for the great info! This really helped me a lot.

    Reply  |  Quote
  12. Dave UNITED STATES Mozilla Firefox Windows says:

    Thanks. I tried several scripts to do this but yours was simplest and most effective.

    Reply  |  Quote
  13. Michael CANADA Mozilla Firefox Windows says:

    Brilliant ! Thank you for posting this. You saved a good portion of what is left of my sanity :)

    I’ve wanted to figure this out for ages.

    Reply  |  Quote
  14. Mandy UNITED ARAB EMIRATES Mozilla Firefox Windows says:

    @ Luke Maciak:
    I am totally confused. I’ve been trying to find a script that works for me. I’ve tried several only to come to a dead end on all of them. Maybe I’m still a newbie :( I want my users to simply click a text link to download the CSV file. But I can’t work it out :(
    - the text link is in my file “status.php”
    - I have your function script in a file called “csv_export.php”
    Do I add the above script into status.php or another page?
    How do I call the function when the link is clicked??

    Sorry for what may seem an obvious answer :(

    Reply  |  Quote
  15. Luke Maciak UNITED STATES Mozilla Firefox Linux Terminalist says:

    @ Mandy:

    Ok, try this. In status.php just make a regular HTML link to csv_export.php. Then in csv_export.php do this:

    <?php 
     
    // copy and paste my function here
     
    // fill this out with your db username, password, etc
    $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');
    mysql_select_db('my_database');
     
    // run an appropriate query
    $result = mysql_query("SELECT * FROM my_table");
     
    // pass the result to my function:
    csv_from_mysql_resource($result);
     
    ?>

    Make sure you don’t have any blank spaces or empty lines above or below the <?php and ?> – that will cause the script to throw errors about headers being already set.

    I hope this helps.

    Reply  |  Quote
  16. Avay NEPAL Mozilla Firefox Windows says:

    This code export all the data whenwe click in the page. But I want to have a link or click button on the right most part of each record. After clicking it I want to execute the function(expor to csv file). Can any one give idea how to use it in this case?

    Reply  |  Quote
  17. Glenn Geiger UNITED STATES Mozilla Firefox Windows says:

    Works like a charm! Thank you for sharing this valuable snippet.

    As a side, I wanted to allow the user to submit a query, for example a date range, and view the results on the screen. Then as an option, click a link to export that data.

    I’m doing it by attaching the query to the URL and retrieving it with GET. Here’s my link:
    <a href="export.php?query=">Export this
    I’ve got your function in a functions.php file. The export.php file looks like this:

    No Query in the URL

    It’s working, but if anyone has a suggestion how I can improve on this, please let me know.

    Reply  |  Quote
  18. Glenn Geiger UNITED STATES Mozilla Firefox Windows says:

    Sorry, the code tags are not working here. If anyone wants to see what I meant, you can contact me through my website.

    Reply  |  Quote
  19. Hans UNITED STATES Mozilla Firefox Windows says:

    Thanks for tips

    Reply  |  Quote
  20. Prashanth INDIA Mozilla Firefox Windows says:

    Dear Luke,

    Thanks for your code.
    Here is a small error on server.But works well in local host.

    Warning: Cannot modify header information – headers already sent by (output started at /home/xxx/public_html/xxx.php:1) in /home/xxx/public_html/xxx.php on line 24

    Please solve this issue. I tried many ways but in vain. please help

    Reply  |  Quote
  21. That warning means that you used a PHP header() declaration to try and set some page headers AFTER you output some HTML to the server. PHP is reminding you that all page headers and such must be done before any HTML is sent to the client because once the HTML is sent to the client, it’s no longer on the server and no server-side scripting can be done at that point.

    Reply  |  Quote

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>