Converting MySQL data into CSV

A question from alt.php:

How to convert MYSQL data result to csv file format.

The high-performance solution is to run a SELECT INTO OUTFILE query:

http://dev.mysql.com/doc/refman/4.1/en/select.html

The drawback of this approach is that the CSV file can only be created on the machine where MySQL server is running, which may or may not be feasible in your particular case.

The alternative is to simply dump the data into a file:

// Assume that the DB connection has already been established...
$result = mysql_query('SELECT * FROM myTable');
$fp = fopen('data.csv', 'w');
if ($fp == false) {
  die("Could not open data.csv for writing");
}
while ($record = mysql_fetch_row($result)) {
  fputcsv($fp, $record);
}
fclose($fp);

Note that fputcsv() is available only since PHP 5.1, so you may have to write your own CSV formatter if you have an earlier version of PHP…

* * * * *

A related question from another poster:

i added a link to download the file created with the url of the file. when i run it on my wamp server i click on the link and it prompts me to save the file, when i run it on my web server the csv opens in the browser instead of prompting to save the file.

any idea of how to solve this?

does it have to do with the php.ini settings?

No, it has to do with HTTP headers Web servers send out before serving actual data.

How do you solve this? By writing a script that would send the appropriate headers and then dump the data. Let’s assume that, in line with the previous example, you have created a file called data.csv. Now you can write a script that will serve this data and suggest to the browser that the data is to be saved as data.csv rather than viewed in the browser:

header('Content-type: application/vnd.ms-excel');
header('Content-disposition: attachment; filename=data.csv');
readfile('data.csv');

Note, however, the word “suggest” above; the browser may choose to ignore the “Content-disposition:” header and still display data in browser of save it under the script’s name rather than as data.csv…

This entry was posted in Answers, Technology. Bookmark the permalink.

Leave a Reply

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