Error while building XLS from MySQL data in PHP

Question

I have some code that written in php to use for export data from mysql to xls, but I have something wrong with the output php.

here's the code

<?php
// koneksi database
$db = "k8665xxxx";

mysql_connect("localhost","k866xxx","kixxx");
mysql_select_db("$db");

$date = date('d-m-Y');
$filename = "Data-inquiry-downloaded-$date.xls";

// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
$table = "promo";


//jumlah kolom
$jkolom=0;

//generate kolom
$q= mysql_query("select * from $table where last_update between '$_POST[DariTanggal]' AND '$_POST[SampaiTanggal]'");
$r=mysql_fetch_assoc($q);
foreach ($r as $head=>$nilai) {
$judul = str_replace("Promo","Inquiry","$head");
$header .=$judul."\t"; //print header table
$jkolom++;
}
$header .= "\n";

//generate baris
$result= mysql_query("select * from $table where last_update between '$_POST[DariTanggal]' AND '$_POST[SampaiTanggal]'");
while ($row=mysql_fetch_array($result)) {
for ($x=0; $x<$jkolom; $x++) {
$content .=$row[$x]."\t";
}
$content .= $row[$x]."\n";
}


$output .= $header.$content;
header('Content-type:application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;

?>

And here's the output...

622 Joni    bekasi  2193850955      4/11/2012                   spooring        yani    TELP        Bekasi  deal
623 rengko      81383691374 cek distributor komo    4/11/2012                   33x12.5x15  ban rosi    TELP        DKI Jakarta 
624 Joko Prayitno               4/11/2012                   175/65/14   ban rosi    EMAIL   [email protected]    DKI Jakarta 
625 diana   Jl.landas pacu timur blok D No.5kemayoran   818772885       4/11/2012   B1603BFF    Honda   2009    jazz Rs ban 185/55/R16  ban yani    YM  [email protected] Jakarta Pusat   deal
626 Indra   Jln.Terusan I Gusti NgurahRay Pondok Kopi                                                       
    8129496491      4/11/2012   B8723KF Suzuki  2004    baleno  paket dasi  paket dasi  rosi    TELP    [email protected]   Jakarta Timur   deal        
627 ari depok   2128639500  mau konfirmasi lagi     4/11/2012                   195/60/15   ban rosi    TELP        Depok   

Let me get the details, look at the number 626.. the data is like this

626 Indra   Jln.Terusan I Gusti NgurahRay Pondok Kopi                                                       
    8129496491      4/11/2012   B8723KF Suzuki  2004    baleno  paket dasi  paket dasi  rosi    TELP    [email protected]   Jakarta Timur   deal        

But, it must be...

626 Indra   Jln.Terusan I Gusti NgurahRay Pondok Kopi  8129496491      4/11/2012   B8723KF Suzuki  2004    baleno  paket dasi  paket dasi  rosi    TELP    [email protected]   Jakarta Timur   deal 

And lot of my data become like that.. anybody have any idea about my problem?

Answer 1

I guess you have a rowbreak in the text in your database, at the 2 rows like $content .=$row[$x]."\t";, use str_replace() to remove \n, or use php:s built in csv functions (like Phil) suggested.

If you want to build more complex xls, not just data in csv, you may want to read about the "Excel XML 2003"-files

$content .= str_replace("\n", " ", $row[$x]) . "\t";

Update

or just trim away all unnecessary whitespace

$content .= trim($row[$x]) . "\t";