Create a CSV file from MySQL with PHP

There are a couple of ways to export data from MySQL to a CSV file 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.

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.