Jory's blog

A while ago at work, a customer asked that we add an export of a specific table in his database to his website. It wasn’t a difficult export, just get every row in the database and output it in a format MS Excel understands. I was assigned the task, so I went at it.

I started to just get the data out of the database. That meant having something like this:

require 'php/bootstrap.php';
$rResultset = $oDatabase->query("SELECT * FROM mytable");

Simple enough, right? The next thing to do was decide what format the export should be in. All they said was a format MS Excel can understand, so the simples thing I could think of was the CSV format. And since I had recently used fgetcsv to read some stuff from a csv export, I took a wild guess and typed fputcsv in my editor.

So, I had to take the data I already got from the database, create a csv file, and send that to the user with the correct HTTP headers. Easy enough, that means this:

// Put the data in a file
$rHandle = fopen("export.csv", "w+");
while($aResult = $oDatabase->fetch($rResultset)) {
    fputcsv($rHandle, $aResult);
}
 
// Send the headers
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=mytable-export.csv");
header("Pragma: no-cache");
header("Expires: 0");
 
// Read the file, from the beginning
rewind($rHandle);
 while (($sOutput = fgets($rHandle, 4096)) !== false) {
        echo $sOutput;
 }

Okay, so that should work pretty well. Unless two people run the export at the same time. Better make the filename unique. Oh but then I’ll get huge load of files in my tmp directory, better delete the file afterwards.

Luckily, at this point, I hadn’t actually written any code, it was all just pseudo-code in my head. Writing to a file just so I can output it, and then delete that file… that’s stupid. (Not to mention a waist of IO, which is expensive enough as it is without me throwing it out the door like this.)

This meant I had a mission: I wanted to output the data right away, but using fputcsv since I don’t want to bother with all the csv stuff myself.

Enter fopen(“php://output”, “w”); which allows you to write to stdout (read: the webserver, read: the browser) as if it where a file. With that, my script became as simple as this:

// Send the headers
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=mytable-export.csv");
header("Pragma: no-cache");
header("Expires: 0");
 
$rHandle = fopen("php://output", "w");
while($aResult = $oDatabase->fetch($rResultset)) {
    fputcsv($rHandle, $aResult);
}

Rather simple if you ask me. And to think I considered messing with files.

After that, the script needed some tweaking: as first row, they wanted the column names. And “America style” csv (where the separator as a comma) doesn’t work here in Europe, since we use the comma for decimal numbers. Neither of which was a problem:

$bHeadersDone = false;
$rHandle = fopen("php://output", "w");
while($aResult = $oDatabase->fetch($rResultset)) {
     if(!$bHeadersDone) {
          $bHeadersDone = true;
          fputcsv($rHandle, array_keys($aResult), ";");
    }
    fputcsv($rHandle, $aResult);
}

Et voila, it does exactly what the customer wants, its just 29 lines long, and it learned me something new. 🙂