Posted By

johnself on 03/01/07


Tagged

mysql php csv export


Versions (?)

Who likes this?

32 people have marked this snippet as a favorite

luman
vali29
peteypablonz
cubit
motoroller
pixelhandler
yoren
traktorist
irishsk
rinx
kiwami
nb109
Hollow
jcroom
ajaypillarisetti
Gr33d
kaartz
huylam
joethermal
osirisinternet
all3n
dinkan
suibhne
bobbym245
adth
pablo808
tspitzr
mfigge
lfcortes
qubestream
ianmone
dennywalker


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.