使用PHP代码将MySQL表导出为CSV文件

Question

我有一张名为pvdata的MySQL表格，我想将它导出为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> 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"""

我的PHP代码如下：

  <?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();

?>

那么出了什么问题呢？ 如果我从phpmyadmin手动导出表格，它可以正常工作。 如果可能的话，我还想获取表格中列的名称。

- maggie

---

由于您在标题和数据中的引号放置不当。 - webrama.pl

Answer 1

在for循环初始化之前，将$header设置为空字符串。

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

    echo $header;//remove this line
}

编辑

同时在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 .= trim( $line ) . "\n";
}

- Harish Ambady

---

在开始时，$header 不是空的吗？在 PHP 中，变量在使用之前不必初始化... - webrama.pl

@PAM变量需要初始化，因为它与.=连接运算符一起使用。 - Harish Ambady

@HarishAnchu 是的，现在好多了，但是单元格仍然没有分开，并且一些列名以任意顺序重复了多次。 - maggie

单元格没有分开，因为您没有在数据之间指定任何分隔符。 - Harish Ambady

@HarishAnchu 是的，我注意到了。我该如何指定分隔符，而不是使用 " 或 ""？ - maggie

Answer 2

I don't know if it is a typo, but within your script are spaces before the

   <?php

this will result in an html document being delivered and your header-calls will fail. So remove the spaces and any other output before "header" calls.

You are getting error messages up there, for a quick solution try:

error_reporting(0);

Line 28: $header is not known, this makes

$header .=

appending to a unknown variable.

- Sebastian Lange

---

<?php 前面不要有空格 - maggie

Answer 3

尝试这段代码 -

<?php

    // Database Connection

    $host="localhost";
    $uname="root";
    $pass="";
    $database = "a2zwebhelp"; 

    $connection=mysql_connect($host,$uname,$pass); 

    echo mysql_error();

    //or die("Database Connection Failed");
    $selectdb=mysql_select_db($database) or 
    die("Database could not be selected"); 
    $result=mysql_select_db($database)
    or die("database cannot be selected <br>");

    // Fetch Record from Database

    $output = "";
    $table = ""; // Enter Your Table Name 
    $sql = mysql_query("select * from $table");
    $columns_total = mysql_num_fields($sql);

    // Get The Field Name

    for ($i = 0; $i < $columns_total; $i++) {
    $heading = mysql_field_name($sql, $i);
    $output .= '"'.$heading.'",';
    }
    $output .="\n";

    // Get Records from the table

    while ($row = mysql_fetch_array($sql)) {
    for ($i = 0; $i < $columns_total; $i++) {
    $output .='"'.$row["$i"].'",';
    }
    $output .="\n";
    }

    // Download the file

    $filename = time().'csv'; //For unique file name
    header('Content-type: application/csv');
    header('Content-Disposition: attachment; filename='.$filename);

    echo $output;
    exit;

    ?>

参考 - 使用PHP将MySQL表导出为CSV

- Rohit Suthar

Answer 4

你可以使用 fputcsv 函数来编写清晰的代码。

- martian