Export MySQL query results to CSV


 / Published in: PHP
 

  1. // Export to CSV
  2. if($_GET['action'] == 'export') {
  3.  
  4. $rsSearchResults = mysql_query($sql, $db) or die(mysql_error());
  5.  
  6. $out = '';
  7. $fields = mysql_list_fields('database','table',$db);
  8. $columns = mysql_num_fields($fields);
  9.  
  10. // Put the name of all fields
  11. for ($i = 0; $i < $columns; $i++) {
  12. $l=mysql_field_name($fields, $i);
  13. $out .= '"'.$l.'",';
  14. }
  15. $out .="\n";
  16.  
  17. // Add all values in the table
  18. while ($l = mysql_fetch_array($rsSearchResults)) {
  19. for ($i = 0; $i < $columns; $i++) {
  20. $out .='"'.$l["$i"].'",';
  21. }
  22. $out .="\n";
  23. }
  24. // Output to browser with appropriate mime type, you choose ;)
  25. header("Content-type: text/x-csv");
  26. //header("Content-type: text/csv");
  27. //header("Content-type: application/csv");
  28. header("Content-Disposition: attachment; filename=search_results.csv");
  29. echo $out;
  30. }

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: tylerhall on March 1, 2007

In some situations it might also be useful to force the user's browser to download the file. Adding these lines should do the trick.

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition: attachment; filename = $filename");
header("Content-Length: " . strlen($out));
Posted By: nevbear666 on January 14, 2009

mysqllistfields is deprecated...

i used this one instead:

[code] mysqlquery("SELECT fieldname1,fieldname2,fieldname3...,fieldnameN FROM tablename"); [/code]

Posted By: killy on August 31, 2009

how to make exported stay on server??

Posted By: stephenwashburn on August 31, 2009

@killy, if you want the results to be on the server instead of sent to the client you will want to use fopen() and fwrite() in order to write the contents to a local file (obviously the file/folder will need to be writable by whatever user you are running php as - I believe its generally the apache user, depending on your setup of course).

Posted By: ajaypillarisetti on September 8, 2009

i'm kinda new to php. i help run a competition with teams from schools all over the country. i've got a page that allows a user to select a variable [a specific school, in this case] from a dropdown. this variable is then part of a query that returns some results specific information about the number of teams from the selected school and outputs them to a table. I'd like to be able to export that same query to csv. Do you all have any thoughts on how I could do that? Thanks much.

Posted By: ajaypillarisetti on September 8, 2009

i'm kinda new to php. i help run a competition with teams from schools all over the country. i've got a page that allows a user to select a variable [a specific school, in this case] from a dropdown. this variable is then part of a query that returns some results specific information about the number of teams from the selected school and outputs them to a table. I'd like to be able to export that same query to csv. Do you all have any thoughts on how I could do that? Thanks much.

You need to login to post a comment.