Create a CSV file from MySQL with PHP ~ Tech Blog

There are a couple of ways to export data from MySQL to a CSV file (refer to my using mysqldump to save data to CSV files and export data to CSV from MySQL posts for details) but neither of them supports adding a header row to the CSV which contains the column names. This post looks at how to export the data from MySQL into a CSV file with PHP including a header row.

Up to date code

I've posted a more up to date way of doing this using fputcsv and PDO (on December 8th 2015) but have kept the old original post here for archival purposes. Read the newer article here: Send CSV data to the browser from MySQL & PHP with fputcsv.

Example function

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. In tomorrow's post I'll show how to send the correct headers etc for sending a CSV file to the web browser with PHP.

Tech Blog

Monday, 10 September 2018