PHP代码将MySQL查询转换为CSV

Question

PHP代码将MySQL查询转换为CSV

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

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

CSV还应该包括一行字段名。

Answer 1

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

Answer 2

对@jrgns的更新(有一些细微的语法差异)解决方案。$result&nbsp;=&nbsp;mysql_query('SELECT&nbsp;*&nbsp;FROM&nbsp;`some_table`');&nbsp;if&nbsp;(!$result)&nbsp;die('Couldn\'t&nbsp;fetch&nbsp;records');&nbsp;$num_fields&nbsp;=&nbsp;mysql_num_fields($result);&nbsp;$headers&nbsp;=&nbsp;array();&nbsp;for&nbsp;($i&nbsp;=&nbsp;0;&nbsp;$i&nbsp;<&nbsp;$num_fields;&nbsp;$i++)&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$headers[]&nbsp;=&nbsp;mysql_field_name($result&nbsp;,&nbsp;$i);&nbsp;}&nbsp;$fp&nbsp;=&nbsp;fopen('php://output',&nbsp;'w');&nbsp;if&nbsp;($fp&nbsp;&&&nbsp;$result)&nbsp;{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;header('Content-Type:&nbsp;text/csv'); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;header('Content-Disposition:&nbsp;attachment;&nbsp;filename="export.csv"'); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;header('Pragma:&nbsp;no-cache');&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;header('Expires:&nbsp;0'); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fputcsv($fp,&nbsp;$headers);&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;while&nbsp;($row&nbsp;=&nbsp;mysql_fetch_row($result))&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fputcsv($fp,&nbsp;array_values($row));&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}die;&nbsp;}