PHP code to convert a MySQL query to CSV [closed]

问题:

What is the most efficient way to convert a MySQL query to CSV in PHP please?

It would be best to avoid temp files as this reduces portability (dir paths and setting file-system permissions required).

The CSV should also include one top line of field names.

回答1:

SELECT * INTO OUTFILE \"c:/mydata.csv\"
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'\"\'
LINES TERMINATED BY \"\\n\"
FROM my_table;

(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = \"SELECT * FROM table_name\";

$export = mysql_query ( $select ) or die ( \"Sql error : \" . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . \"\\t\";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = \'\';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == \"\" ) )
        {
            $value = \"\\t\";
        }
        else
        {
            $value = str_replace( \'\"\' , \'\"\"\' , $value );
            $value = \'\"\' . $value . \'\"\' . \"\\t\";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . \"\\n\";
}
$data = str_replace( \"\\r\" , \"\" , $data );

if ( $data == \"\" )
{
    $data = \"\\n(0) Records Found!\\n\";                        
}

header(\"Content-type: application/octet-stream\");
header(\"Content-Disposition: attachment; filename=your_desired_name.xls\");
header(\"Pragma: no-cache\");
header(\"Expires: 0\");
print \"$header\\n$data\";

回答2:

Check out this question / answer. It\'s more concise than @Geoff\'s, and also uses the builtin fputcsv function.

$result = $db_con->query(\'SELECT * FROM `some_table`\');
if (!$result) die(\'Couldn\\\'t fetch records\');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}
$fp = fopen(\'php://output\', \'w\');
if ($fp && $result) {
    header(\'Content-Type: text/csv\');
    header(\'Content-Disposition: attachment; filename=\"export.csv\"\');
    header(\'Pragma: no-cache\');
    header(\'Expires: 0\');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

回答3:

Look at the documentation regarding the SELECT ... INTO OUTFILE syntax.

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

回答4:

An update to @jrgns (with some slight syntax differences) solution.

$result = mysql_query(\'SELECT * FROM `some_table`\'); 
if (!$result) die(\'Couldn\\\'t fetch records\'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen(\'php://output\', \'w\'); 
if ($fp && $result) 
{     
       header(\'Content-Type: text/csv\');
       header(\'Content-Disposition: attachment; filename=\"export.csv\"\');
       header(\'Pragma: no-cache\');    
       header(\'Expires: 0\');
       fputcsv($fp, $headers); 
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       }
die; 
} 

回答5:

If you\'d like the download to be offered as a download that can be opened directly in Excel, this may work for you: (copied from an old unreleased project of mine)

These functions setup the headers:

function setExcelContentType() {
    if(headers_sent())
        return false;

    header(\'Content-type: application/vnd.ms-excel\');
    return true;
}

function setDownloadAsHeader($filename) {
    if(headers_sent())
        return false;

    header(\'Content-disposition: attachment; filename=\' . $filename);
    return true;
}

This one sends a CSV to a stream using a mysql result

function csvFromResult($stream, $result, $showColumnHeaders = true) {
    if($showColumnHeaders) {
        $columnHeaders = array();
        $nfields = mysql_num_fields($result);
        for($i = 0; $i < $nfields; $i++) {
            $field = mysql_fetch_field($result, $i);
            $columnHeaders[] = $field->name;
        }
        fputcsv($stream, $columnHeaders);
    }

    $nrows = 0;
    while($row = mysql_fetch_row($result)) {
        fputcsv($stream, $row);
        $nrows++;
    }

    return $nrows;
}

This one uses the above function to write a CSV to a file, given by $filename

function csvFileFromResult($filename, $result, $showColumnHeaders = true) {
    $fp = fopen($filename, \'w\');
    $rc = csvFromResult($fp, $result, $showColumnHeaders);
    fclose($fp);
    return $rc;
}

And this is where the magic happens ;)

function csvToExcelDownloadFromResult($result, $showColumnHeaders = true, $asFilename = \'data.csv\') {
    setExcelContentType();
    setDownloadAsHeader($asFilename);
    return csvFileFromResult(\'php://output\', $result, $showColumnHeaders);
}

For example:

$result = mysql_query(\"SELECT foo, bar, shazbot FROM baz WHERE boo = \'foo\'\");
csvToExcelDownloadFromResult($result);

回答6:

// Export to CSV
if($_GET[\'action\'] == \'export\') {

  $rsSearchResults = mysql_query($sql, $db) or die(mysql_error());

  $out = \'\';
  $fields = mysql_list_fields(\'database\',\'table\',$db);
  $columns = mysql_num_fields($fields);

  // Put the name of all fields
  for ($i = 0; $i < $columns; $i++) {
    $l=mysql_field_name($fields, $i);
    $out .= \'\"\'.$l.\'\",\';
  }
  $out .=\"\\n\";

  // Add all values in the table
  while ($l = mysql_fetch_array($rsSearchResults)) {
    for ($i = 0; $i < $columns; $i++) {
      $out .=\'\"\'.$l[\"$i\"].\'\",\';
    }
    $out .=\"\\n\";
  }
  // Output to browser with appropriate mime type, you choose ;)
  header(\"Content-type: text/x-csv\");
  //header(\"Content-type: text/csv\");
  //header(\"Content-type: application/csv\");
  header(\"Content-Disposition: attachment; filename=search_results.csv\");
  echo $out;
  exit;
}