用'，'从数据库中提取数据到csv文件

Question

这是我从数据库中提取数据到csv文件的代码。它工作正常，但现在我把它放在我的程序中，数据有地址，因此有逗号（，），我在打印时遇到问题。

这是它的样子：

Name  Address1   Address2   City   Email 
Name1  123 Mcarthur  San Juan City 12 Jones  Manila 

它应该是这样的：

Name  Address1      Address2   City   Email  
Name1 123 Mcarthur, San Juan City 12 Jones, Manila  Manila  [email protected] 

城市和电子邮件都推到另一列。我搜索了互联网，但我看到的所有数据都带有逗号（，）。

另外，php中的警告和错误消息也在csv文件中导出。我没有那么多问题，因为一旦我解决了这个问题，我认为不会有任何警告。

然后，这是我的代码：

 if(isset($_POST['csvextract'])){ 
     $name = "sample"; 
     $file = $name.".csv"; 

      $con = mysql_connect("localhost", "root"); 
      if(!$con){ 
       echo "Error connection"; 
        } 

      $select_db = mysql_select_db('outbound', $con); 
      if(!$select_db){ 
       echo "Error to select database"; 
        } 
      mysql_set_charset("utf8", $con); 

      header("Content-type: text/csv; charset=UTF-8"); 
      header('Content-disposition: attachment; filename='.basename($file)); 

      $myquery = mysql_query("SELECT * FROM temp"); 

     //While loop to fetch the records 
     $contents = "Name, Company, Address1, Address2, City, Province, Postal Code, Contact No, Email, Commodity, Type, Weight, Length, Width, Height, Decreased Value, Special Instruction, Shipping Reference, Payment Method, Package No, Tracking No\n"; 
     while($row = mysql_fetch_array($myquery)) 
     { 
      $contents.=$row['0'].","; 
      $contents.=$row['1'].","; 
      $contents.=$row['2'].","; 
      $contents.=$row['3'].","; 
      $contents.=$row['4'].","; 
      $contents.=$row['5'].","; 
      $contents.=$row['6'].","; 
      $contents.=$row['7'].","; 
      $contents.=$row['8'].","; 
      $contents.=$row['9'].","; 
      $contents.=$row['10'].","; 
      $contents.=$row['11'].","; 
      $contents.=$row['12'].","; 
      $contents.=$row['13'].","; 
      $contents.=$row['14'].","; 
      $contents.=$row['15'].","; 
      $contents.=$row['16'].","; 
      $contents.=$row['17'].","; 
      $contents.=$row['18'].","; 
      $contents.=$row['19'].","; 
      $contents.=$row['20'].","; 
      $contents.=$row['21']."\n"; 
     } 

     $contents_final = chr(255).chr(254).mb_convert_encoding($contents, "UTF-16LE","UTF-8"); 
     print $contents_final; 
     } 

Answer 1

我不知道，如果你有这个已经工作。但是我对你的代码做了一些改动。

下面是代码：

if (isset($_POST['csvextract'])) { 
$name = "sample"; 
$file = $name . ".csv"; 
$delimiter = ','; 

$con = mysql_connect("localhost", "root"); 
if (!$con) { 
    echo "Error connection"; 
    exit; 
} 

$select_db = mysql_select_db('outbound', $con); 
if (!$select_db) { 
    echo "Error to select database"; 
    exit; 
} 
mysql_set_charset("utf8", $con); 

header("Content-type: text/csv; charset=UTF-8"); 
header('Content-disposition: attachment; filename=' . basename($file)); 

$myquery = mysql_query("SELECT * FROM temp"); 

//While loop to fetch the records 
$contents = "Name, Company, Address1, Address2, City, Province, Postal Code, Contact No, Email, Commodity, Type, Weight, Length, Width, Height, Decreased Value, Special Instruction, Shipping Reference, Payment Method, Package No, Tracking No\n"; 
while ($row = mysql_fetch_array($myquery)) { 

    for ($i = 0; $i < 22; $i++) { 
     if (false === strpos($row[$i], $delimiter)) { 
      $contents .= '"' . $row[$i] . '"'; 
     } else { 
      $contents.=$row[$i]; 
     } 
     if ($i === 21) { 
      $contents .= "\n"; 
     } else { 
      $contents .= $delimiter; 
     } 
    } 
} 

$contents_final = chr(255) . chr(254) . mb_convert_encoding($contents, "UTF-16LE", "UTF-8"); 
print $contents_final; 
} 

我改下。

1. 我在脚本的顶部添加了一个分隔符变量。通过这个，你可以在分隔符之间轻松切换，所以如果它改变了，你可以通过编辑1行来做到这一点。
2. 而不是我使用for循环的所有行，因为每个表只是一个数字。
3. 添加一个检查，以查看在字符串中包含的分隔符，如果是把qoutes周围
4. 检查是否$i是21，因为这时我们需要去为新的线路。
5. 添加退出时，没有DB连接或没有数据库可以选择

如果这不起作用，请让我知道，我会解决它。注意我没有试过这个代码我的自我。

Answer 2

使用此：

echo "Name, Company, Address1, Address2, City, Province, Postal Code, Contact No, Email, Commodity, Type, Weight, Length, Width, Height, Decreased Value, Special Instruction, Shipping Reference, Payment Method, Package No, Tracking No\n"; 
while ($row = mysql_fetch_row($myquery)) { 
    fputcsv(STDOUT, $row); 
}