I am new to PHP. I just want to export data(the search result which I got from querying the database) to excel. My problem is the data in excel will automatically change a new line. I want 'cn' 'ai'... in the next column but not next line.
I already removed all the $csv_output .= "\r"; in my code.
So I thing there might be something wrong with my query statement:
$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"
I am new to PHP and MySQL. Please give me some hint about this problem. Thanks!
<?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;
?>
mysql_queryin any new applications because it's being retired, and further, it leads to code exactly like this, full of bugs and security problems. It doesn't take long to learn how to escape SQL properly with PDO ormysqliand you'll be safe and secure from that point forward. Step one of learning how to use MySQL with PHP is learning how NOT TO usemysql_query.