从MySQL数据库导出数据到PHP

Question

我是PHP新手。我只想将数据(从查询数据库中获得的搜索结果)导出到excel。我的问题是，excel中的数据将自动更改新行。我想要'cn‘’ai‘.在下一列，而不是下一行.

我已经删除了代码中的所有$csv_output .= "\r";。所以我想我的查询语句可能有问题：

$searchp = "
    select 
        * 
    from 
        insecticide a 
            LEFT JOIN crop b 
                ON a.ID = b.ID 
    where 
        a.Pestcide_trade_name like'%$tn%' 
        and a.Pestcide_trade_name like'%$tn2%' 
        and b.Crops like'%$crops%' 
        and b.Crops like'%$crops2%' 
        and a.AMW like'%$amw%' 
        and b.Pests like'%$pest%' 
        and a.Company_name like'%$cn%' 
        and a.Company_name like'%$cn2%' 
        and a.Active_ingredient like'%$ai%' 
        and a.Active_ingredient like'%$ai2%' 
        and a.PPE like'%$ppe%' 
        and a.PPE like'%$ppe2%' 
        and a.MMW like'%$mmw%' 
        and a.EH like'%$eh%' 
        and b.REI like'%$rei%' 
        and b.REI like'%$rei2%' 
    ORDER BY 
        a.Pestcide_trade_name ASC"

我对PHP和MySQL很陌生。关于这个问题，请给我一点提示。谢谢!

<?php
    require_once("db.php");


                $tn = $_POST['TN']?$_POST['TN']:'';
                $tn2 = $_POST['TN2']?$_POST['TN2']:'';
                $cn = $_POST['CN']?$_POST['CN']:'';
                $cn2 = $_POST['CN2']?$_POST['CN2']:'';
                $ai = $_POST['AI']?$_POST['AI']:'';
                $ai2 = $_POST['AI2']?$_POST['AI2']:'';
                $ppe = $_POST['PPE']?$_POST['PPE']:'';
                $ppe2 = $_POST['PPE2']?$_POST['PPE2']:'';
                $amw  = $_POST['AMW']?$_POST['AMW']:'';
                $mmw  = $_POST['MMW']?$_POST['MMW']:'';
                $eh = $_POST['EH']?$_POST['EH']:'';
                $crops = $_POST['Crops']?$_POST['Crops']:'';
                $crops2 = $_POST['Crop2']?$_POST['Crop2']:'';
                $rei = $_POST['REI']?$_POST['REI']:'';              
                $rei2 = $_POST['REI2']?$_POST['REI2']:'';
                $pest = $_POST['PEST']?$_POST['PEST']:'';
                $rate = $_POST['RATE']?$_POST['RATE']:'';
                $note = $_POST['Note']?$_POST['Note']:'';
                $restrictions = $_POST['Restrictions']?$_POST['Restrictions']:'';    
        $searchp = "select * from insecticide a LEFT JOIN crop b ON a.ID = b.ID where a.Pestcide_trade_name like'%$tn%' and a.Pestcide_trade_name like'%$tn2%' and b.Crops like'%$crops%' and b.Crops like'%$crops2%' and a.AMW like'%$amw%' and b.Pests like'%$pest%' and a.Company_name like'%$cn%' and a.Company_name like'%$cn2%' and a.Active_ingredient like'%$ai%' and a.Active_ingredient like'%$ai2%' and a.PPE like'%$ppe%' and a.PPE like'%$ppe2%' and a.MMW like'%$mmw%' and a.EH like'%$eh%' and b.REI like'%$rei%' and b.REI like'%$rei2%' ORDER BY a.Pestcide_trade_name ASC";
        $result=mysql_query($searchp)or die(mysql_error()); 
        $num=mysql_numrows($result);
        mysql_close();

       $csv_output = '"Trade name","Company Name","Active Ingredient","PPE","Applicators Must Wear","Mixers Must Wear","Environmental Hazards","CROPS","REI","PEST","RATE","RATE","NOTE","RESTRICTION"\N';

            $i=0;
            while ($i < $num) {

            $tn1 = mysql_result($result,$i,"Pestcide_trade_name");
            $cn1 = mysql_result($result,$i,"Company_name");
            $ai1 = mysql_result($result,$i,"Active_ingredient");
            $ppe1 = mysql_result($result,$i,"PPE");
            $amw1 = mysql_result($result,$i,"AMW");
            $mmw1 = mysql_result($result,$i,"MMW");

            $eh1 = mysql_result($result,$i,"EH");
            $crop1 = mysql_result($result,$i,"Crops");
            $rei1 = mysql_result($result,$i,"REI");
            $pest1 = mysql_result($result,$i,"Pests");
            $rate1 = mysql_result($result,$i,"Rate_FL_OZ_ARCE");
            $rate11 = mysql_result($result,$i,"RATE_LB_ARCE");
            $note1 = mysql_result($result,$i,"Note");
            $restrictions1 = mysql_result($result,$i,"Restrictions");

            $csv_output .= "$tn1";
            $csv_output .="$cn1";
            $csv_output .="$ai1";
            $csv_output .= "$ppe1";
            $csv_output .="$amw1";
            $csv_output .= "$mmw1";
            $csv_output .= "$eh1";
            $csv_output .= "$crop1";
            $csv_output .= "$rei1";
            $csv_output .= "$pest1";
            $csv_output .= "$rate1";
            $csv_output .= "$rate11";
            $csv_output .= "$note1";
            $csv_output .= "$restrictions1";
            $csv_output .= "\n";
            ++$i;
            }


    $filename = "results_".date("Y-m-d_H-i",time());
    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition: csv" . date("Y-m-d") . ".csv");
    header( "Content-disposition: filename=".$filename.".csv");
    print $csv_output;
    exit;

?>

Answer 1

不要自己写CSV代码。请参阅：http://php.net/manual/en/function.fputcsv.php

还有，你缺少分隔符.但不要修正这段代码。重构它。

Answer 2

你忘记了逗号和引号：

        $csv_output .= '"' . $tn1 . '",';
        $csv_output .= '"' . $cn1 . '",';
        // etc.