The example code below uses the raw mysql_* functions but it should be easy enough to substitute a database library’s functions instead. It also writes the data out line by line to the CSV file whereas you could buffer the whole file in memory and write it out at one go; however if the resultset is large it may be better to write it out line by line so as not to consume too much memory.
The $server, $login, $password, $db and $table variables should be obvious in their purpose 🙂===================================================================================================
mysql_connect($server, $login, $password);
mysql_select_db($db);
$fp = fopen($filename, "w");
$res = mysql_query("SELECT * FROM $table");
// fetch a row and write the column names out to the file
$row = mysql_fetch_assoc($res);
$line = "";
$comma = "";
foreach($row as $name => $value) {
$line .= $comma . '"' . str_replace('"', '""', $name) . '"';
$comma = ",";
}
$line .= "\n";
fputs($fp, $line);
// remove the result pointer back to the start
mysql_data_seek($res, 0);
// and loop through the actual data
while($row = mysql_fetch_assoc($res)) {
$line = "";
$comma = "";
foreach($row as $value) {
$line .= $comma . '"' . str_replace('"', '""', $value) . '"';
$comma = ",";
}
$line .= "\n";
fputs($fp, $line);
}
fclose($fp);============================================================================================================================================
Some things to note:
1) The first row is read from the database and used to create the header row in the file. mysql_data_seek is then used to return the result pointer back to the start of the result set and then the rest of the data read.
2) The reason I use the $comma variable is so there isn’t an extra comma at the end of each row with no data, which is what would happen if you did $line .= ‘”‘ . str_replace(‘”‘, ‘””‘, $value) . ‘”,’; and had the comma coded into the variable.
3) The str_replace() call escapes double quotes in a data value so they look like “” which is the correct escaping for CSV data.
The data could then be sent to a web browser by reading from the output file, or instead by buffering the CSV data in memory and simply echo’ing it out to the browser.
Check below to show how to send the correct headers etc for sending a CSV file to the web browser with PHP.
The headers look like so, substituting filename.csv for the name you would like:
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="filename.csv"');Then it’s simply a matter of echo’ing out the CSV data to the web browser.
Compressing the data with ob_gzhandler
In order to cut down the amount of data actually transferred, it’s a good idea to use PHP’s ob_start() in combination with the gzhandler callback which will compress the output before sending it to the browser. The browser decompresses it and you still end up with a plain text file:
ob_start('ob_gzhandler');
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="filename.csv"');Reading from a file
The CSV data will most likely come from a variable, from looping through data from a database or from a file. To echo the contents of the file to the browser do this:
readfile($filename);
where $filename is the filename of the file.
