PHP: generate CSV data from MySQL
Blogs20112011-05-27
While I am doing export mysql data to CSV file by using php, I found a very helpful article: http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv
I simply modified it, and added some comments, to apply into a PHP app, which has a ‘button’ linked to generate CVS file.
$result = mysql_query($_SESSION['sql']);
if (!$result) die(mysql_error());
$num_fields = mysql_num_fields($result);
$headers = array();
/* record field names */
for ($i = 0; $i < $num_fields; $i++) {
$str = mysql_field_name($result , $i);
// for better output, decorate the field names.
$headers[] = __decoration($str);
}
// http://php.net/manual/en/wrappers.php.php, an alternative way is
// to use 'echo'.
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment;
filename="export_'.date("Y-m-d").'.csv"');
header('Pragma: no-cache');
header('Expires: 0');
/* output header field names */
fputcsv($fp, $headers);
/* loop to output data */
while ($row = mysql_fetch_array(MYSQLI_NUM)) {
fputcsv($fp, array_values($row));
}
}It has 3 advantages:
- avoid comma(’,’) conflict in the fields,
- very straightful, can be available as a function,
- instead of scalar variables, use array(), and fputcsv() and array_values() are good choice in such case.