将 MySQL 查询转换为 CSV 的 PHP 代码

在 PHP 中将 MySQL 查询转换为 CSV 的最有效方法是什么？

最好避免使用临时文件，因为这会降低可移植性(目录路径和设置所需的文件系统权限)。

CSV 还应该包括字段名的顶行。

252616 次浏览
小开 
// 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;
}
小开

查看关于 SELECT... INTO OUTFILE 语法的 文件。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
小开
最佳答案 
SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

(关于这一点的文档如下: < a href = “ http://dev.mysql.com/doc/refman/5.0/en/select.html”rel = “ norefrer”> http://dev.mysql.com/doc/refman/5.0/en/select.html )

或:

$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";
小开

看看这个 问题/答案，它比@Geoff 更简洁，而且还使用了内置的 fputcsv 函数。

$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;
}
小开

如果你想提供下载作为一个下载，可以直接在 Excel 中打开，这可能为你工作: (从我的一个未发布的旧项目复制)

这些函数设置标题:

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;
}

这个函数使用 mysql 结果将 CSV 发送到流

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;
}

这个函数使用上面的函数将 CSV 写入文件，文件名为 $filename

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

这就是奇迹发生的地方;)

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

例如:

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

更新到@jrgns (有一些语法上的细微差别)解决方案。

$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;
}

提交答案