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.
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 is a livesaver! thank you!
Whoa, so happy I didn’t have to code this! :)
Awesome. I’m glad you found this useful. :)
This is EXACTLY what I was looking for – and so simple to use.
Thank you!!!!!
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…
replace it with an HTML Line break:
the ‘s got eaten out of my last comment.
I meant: ”
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 :)
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?
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.html") // 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. :)
Thanks Luke, that worked great and really helps with my learning. I will be sure to explore more of your PHP/MySQL posts.
Chris
Thanks for the great info! This really helped me a lot.
Thanks. I tried several scripts to do this but yours was simplest and most effective.
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.
@ 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 :(
@ 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.
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?
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 thisI’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.
Sorry, the code tags are not working here. If anyone wants to see what I meant, you can contact me through my website.
Thanks for tips
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
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.