Exporting MySQL Data to CSV in PHP

Giving your users a way to output their data to a CSV file should be easy and fast to do. It’s not. So here are some notes to quickly get it working.

First, you can leverage some help from MySQL, like so:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

But there are drawbacks to this. The main issue is that it writes the file to the MySQL server’s local file system. If your webserver isn’t on the same box as your database server, that could be a problem: you won’t have access to the database server’s filesystem from your webserver, where your PHP scripts are. 🙁

So we scrap this method.

The best way to do this, and by “best” I mean the one which affords you the most control, is to generate the CSV file yourself, from your own PHP script. In fact, PHP has a handy fputcsv() function, which formats your data as a CSV record, before writing it out to the file.

Your code could then look something like this:

  require_once($_WS_DB . 'customers.php');
  $oCust = new customers();
  $res = $oCust->fetchAll('order by cust_name');

  $fp = fopen($_WS_DOWNLOAD . 'customers.csv', 'w');
  fwrite($fp, 
    '"cust_id","cust_name","cust_email","cust_addr1","cust_addr2","cust_city","cust_state","cust_zip","cust_pw","cust_category"'
    . "\n");
  while ($row = $res->fetch_assoc()) {
    fputcsv($fp, $row);
  }
  fclose($fp);

Of course, you need to ensure your download directory (in this case, stored in the $_WS_DOWNLOAD variable) has permissions suitable for allowing your webserver to write to it.

You may, or may not want the header included in your file. If not, just take out the fwrite() prior to the while loop.

The other thing to keep in mind here is that you may have multiple users wanting the same file. There are a number of strategies available for dealing with this. You could add a random number, or timestamp, at the end of the filename, so each user would get her own copy of the file. (If you use this strategy, remember to have some mechanism to remove obsolete files.) Or, depending on the application, if the data doesn’t change that often, you could have a cron job generate the file every so often, and allow users to either download the current file, or generate a new version then download it, to make sure they have the very latest.

Speaking of downloading, that’s our next step. We want the user to hit a button, or a link, and get a download dialog, without actually moving off of her current page.

To accomplish this, we have the magic of the content-disposition header available to us. Since we require the use of headers here, we need to wrap the file link in a page request. But not to worry, because we specify the disposition as attachment, we don’t actually go to the requested document. So, invoking the page looks like so:


And the servfile.php script is similarly concise:

<?php
require_once('apptop.php');

$file = basename($_REQUEST['f']);

header("Content-Type: text/plain ");
header("Content-Disposition: attachment; filename={$file} ");

flush();
readfile($_WS_DOWNLOAD . $file);
exit;

Please note that the script only allows for files from the download directory. If we allowed arbitrary files to be downloaded, hackers would be able to find all the secrets to our code by invoking, f’rinstance, http://servefile.php?f=apptop.php. An important security precaution.

And that’s pretty much all there is to that. Let me know in the comments if you have any questions.

Leave a Reply

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