William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

PHP: generate CSV data from MySQL

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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: