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 // 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.
Related Posts:
September 20th, 2007 at 3:14 am (6243) [Quote]
this is a livesaver! thank you!
Whoa, so happy I didn’t have to code this!
Posted usingSeptember 20th, 2007 at 9:58 am (6244) [Quote]
Awesome. I’m glad you found this useful.
Posted usingOctober 11th, 2007 at 1:21 pm (6526) [Quote]
This is EXACTLY what I was looking for - and so simple to use.
Thank you!!!!!
Posted usingOctober 30th, 2007 at 11:22 am (6765) [Quote]
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…
Posted usingOctober 30th, 2007 at 11:23 am (6766) [Quote]
replace it with an HTML Line break:
the ’s got eaten out of my last comment.
Posted usingOctober 30th, 2007 at 11:25 am (6767) [Quote]
I meant: ‘’
Posted usingOctober 30th, 2007 at 12:02 pm (6769) [Quote]
Wordpress strips HTML tags by default. If you use the code button you can override that behavior. Either that, or use HTML entities < and > for < and >
Thanks for the tip though
Posted usingNovember 7th, 2007 at 12:34 pm (6867) [Quote]
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)
Posted using” appears - I’m running PHP5 - or else a blank white page. Any chance you can expand on implementing this function?
November 7th, 2007 at 12:54 pm (6868) [Quote]
You want to run your query in the usual way and then you pass the result to the function:
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:
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.
Posted usingNovember 8th, 2007 at 4:36 am (6878) [Quote]
Thanks Luke, that worked great and really helps with my learning. I will be sure to explore more of your PHP/MySQL posts.
Posted usingChris
May 27th, 2008 at 10:20 am (9142) [Quote]
Thanks for the great info! This really helped me a lot.
Posted usingJuly 31st, 2008 at 11:48 am (9750) [Quote]
Thanks. I tried several scripts to do this but yours was simplest and most effective.
Posted using