使用PHP代码将mysql表导出为CSV [英] Export a mysql table into CSV using PHP code
本文介绍了使用PHP代码将mysql表导出为CSV的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个我的sql表称为pvdata，我想导出它到csv文件。
但我获得以下结果，而不是正常的表格：
< br /
< font size ='1'>< table class ='xdebug-error xe-notice'dir ='ltr'border ='1'cellspacing ='0'cellpadding ='1'
< tr>< th align ='left'bgcolor ='＃f57900'colspan =5>< span style ='background-color：＃cc0000; color：＃fce94f; font-size：x-large;'>（！）< / span>注意：未定义的变量：标题C：\wamp\www\EXPORT TABLE\index.php on line< i> 28< / i>< / th>< / tr>
< tr>< th align ='left'bgcolor ='＃e9b96e'colspan ='5'> Call Stack< / th>< / tr>
< tr>< th align ='center'bgcolor ='＃eeeeec'>＃< / th> th align ='left'bgcolor ='＃eeeeec'> Time< / th ;< th align ='left'bgcolor ='＃eeeeec'> Memory< / th>< th align ='left'bgcolor ='＃eeeeec'> Function< / th>< th align = 'bgcolor ='＃eeeeec'>位置< / th>< / tr>
< tr>< td bgcolor ='＃eeeeec'align ='center'> 1< / td>< td bgcolor ='＃eeeeec'align ='center'> 0.0035< / td ;< td bgcolor ='＃eeeeec'align ='right'> 256088< / td>< td bgcolor ='＃eeeeec'> {main}（）< / td>< td title ='C ：\wamp\www\EXPORT TABLE\index.php'bgcolor ='＃eeeeec'> .. \index.php< b>：< / b> 0< / td>< / tr> ;
< / table>< / font>
id id状态id状态经度id状态经度纬度id状态经度纬度高度（km）id状态经度纬度高度（km）模块倾斜id状态经度纬度高度（km）模块倾斜模块方位角id状态经度纬度海拔（km）模块倾斜模块方位角额定峰值功率（kW）id状态经度纬度高度（km）模块倾斜模块方位角额定峰值功率（kW）温度损失系数id状态经度纬度高度（km）模块倾斜模块方位角额定峰值功率kW）温度损失系数标称工作单元温度id状态经度纬度高度（km）模块倾斜模块方位角额定峰值功率（kW）温度损失系数标称工作温度电池温度逆变器效率id状态经度纬度高度（km）模块倾斜模块方位角额定峰值功率（kW）温度损失系数额定工作电池温度逆变器效率Persil Name< br />
< font size ='1'>< table class ='xdebug-error xe-notice'dir ='ltr'border ='1'cellspacing ='0'cellpadding ='1'
< tr>< th align ='left'bgcolor ='＃f57900'colspan =5>< span style ='background-color：＃cc0000; color：＃fce94f; font-size：x-large;'>（！）< / span>注意：未定义的变量：第< i>行< i>< i>< / th>< / tr>上的C：\wamp\www\EXPORT TABLE\index.php中的数据
< tr>< th align ='left'bgcolor ='＃e9b96e'colspan ='5'> Call Stack< / th>< / tr>
< tr>< th align ='center'bgcolor ='＃eeeeec'>＃< / th> th align ='left'bgcolor ='＃eeeeec'> Time< / th ;< th align ='left'bgcolor ='＃eeeeec'> Memory< / th>< th align ='left'bgcolor ='＃eeeeec'> Function< / th>< th align = 'bgcolor ='＃eeeeec'>位置< / th>< / tr>
< tr>< td bgcolor ='＃eeeeec'align ='center'> 1< / td>< td bgcolor ='＃eeeeec'align ='center'> 0.0035< / td ;< td bgcolor ='＃eeeeec'align ='right'> 256088< / td>< td bgcolor ='＃eeeeec'> {main}（）< / td>< td title ='C ：\wamp\www\EXPORT TABLE\index.php'bgcolor ='＃eeeeec'> .. \index.php< b>：< / b> 0< / td>< / tr> ;
< / table>< / font>id状态经度纬度海拔高度模块倾斜模块方位角额定峰值功率温度损失系数额定工作电池温度逆变器效率Persil Name
1persil000000.004747 PERSIL07
2其他121201515012 0.0046450.95预定义
代码是：
<？php
$ username =root
$ password =;
$ hostname =localhost;
$ dbhandle = mysql_connect（$ hostname，$ username，$ password）
or die（Unable to connect to MySQL）;
$ selected = mysql_select_db（user data smart grid，$ dbhandle）
或die（Could not select Data Base）;
header（Content-type：application / csv）;
header（Content-Disposition：attachment; filename = pvdata.csv）;
header（Pragma：no-cache）;
header（Expires：0）;
$ query =SELECT * FROM pvdata;
$ export = mysql_query（$ query）或die（Sql error：。mysql_error（））;
$ fields = mysql_num_fields（$ export）;
for（$ i = 0; $ i <$ fields; $ i ++）
{
$ header。= mysql_field_name（$ export，$ i）。 \t;
echo $ header;
}
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。 ;
}
$ data = str_replace（\r，，$ data）;
if（$ data ==）
{
$ data =\\\
（0）Records Found！\\\
;
}
print$ header\\\
$ data;
exit（）;
？>
如果我从phpmyadmin手动导出表，它工作正常
我也想获得表中的列的名称，如果可能。
$ header =''; //初始化头
for（$ i = 0; $ i <$ fields; $ i ++）
{
$ header。= mysql_field_name（$ export，$ i）。\t;
echo $ header; // remove this line
}
EDIT
同时在while循环之外初始化$ data。
$ data ='';
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。 ;
}
I have a my sql table called pvdata, I would like to export it to csv file.
But I'm obtaining the following results instead of the normal looking table:
<br />
<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1'cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Notice: Undefined variable: header in C:\wamp\www\EXPORT TABLE\index.php on line <i>28</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0035</td><td bgcolor='#eeeeec' align='right'>256088</td><td bgcolor='#eeeeec'>{main}( )</td><td title='C:\wamp\www\EXPORT TABLE\index.php' bgcolor='#eeeeec'>..\index.php<b>:</b>0</td></tr>
</table></font>
"id id state id state Longitude id state Longitude Latitude id state Longitude Latitude Altitude(km) id state Longitude Latitude Altitude(km) Module Tilt id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) Temperature losses coefficient id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) Temperature losses coefficient Nominal Operation Cell Temperature id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) Temperature losses coefficient Nominal Operation Cell Temperature Invertor Effeciency id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) Temperature losses coefficient Nominal Operation Cell Temperature Invertor Effeciency Persil Name <br />"
<font size='1'><table class='xdebug-error xe-notice' dir='ltr' border='1' cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Notice: Undefined variable: data in C:\wamp\www\EXPORT TABLE\index.php on line <i>49</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0035</td><td bgcolor='#eeeeec' align='right'>256088</td><td bgcolor='#eeeeec'>{main}( )</td><td title='C:\wamp\www\EXPORT TABLE\index.php' bgcolor='#eeeeec'>..\index.php<b>:</b>0</td></tr>
</table></font>"id state Longitude Latitude Altitude(km) Module Tilt Module Azimuth Rated Peak Power(kW) Temperature losses coefficient Nominal Operation Cell Temperature Invertor Effeciency Persil Name "
"1 ""persil"" ""0"" ""0"" ""0"" ""0"" ""0"" ""0"" ""0.0047"" ""47"" ""0.9"" ""PERSIL07"""
"2 ""other"" ""12"" ""12"" ""0"" ""15"" ""150"" ""12"" ""0.0046"" ""45"" ""0.95"" ""predefined"""
My php code is:
<?php
$username = "root";
$password = "";
$hostname = "localhost";
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
$selected = mysql_select_db("user data smart grid",$dbhandle)
or die("Could not select Data Base");
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=pvdata.csv");
header("Pragma: no-cache");
header("Expires: 0");
$query = "SELECT * FROM pvdata";
$export = mysql_query ($query ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
echo $header;
}
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";
}
print "$header\n$data";
exit();
?>
so what's going wrong? If I export the table manually from the phpmyadmin, it works fine. I also want to get the names of the columns in the table if possible.
解决方案
Before for loop initialize $header to empty string.
$header = '';//initialize header
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
echo $header;//remove this line
}
EDIT
Also initialize $data outside while loop.
$data = '';
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";
}
这篇关于使用PHP代码将mysql表导出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
