Using PHP to Stream SQL results dynamically as CSV, even to IE

Jun 6, 2011 · 1 minute read
Category: php

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

If you want to offer a csv feed and want the results to be on demand and like the idea of avoiding messing about righting to files on the server etc, you might like this little manuever.

You will notice I’m using a non standard db_query function, just assume that this does all the sensible stuff it should.

Also the query needs to be modified to suit.

The trick is writing to PHP’s built in php://output stream which is basically just STDOUT.

Note the headers which are specifically required to get IE to accept the file as a download. Other browsers will accept less headers but will also work fine with these.

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"file.csv\";");
header("Content-Transfer-Encoding: binary");

$outstream = fopen("php://output", 'w');

$sql = " select * from a_table";
$q = db_query("$sql");

$header = false;
while ($r = mysql_fetch_assoc($q)) {
    if (!$header) {
        fputcsv($outstream, array_keys($r));
        $header = true;
    }
    fputcsv($outstream, $r);
}
fclose($outstream);