MYSQL to excel....exporting only one column

[davidcriniti]

Hi everyone,

 

I got a bit of code off the web and did some tweaking. I want to use it to export data from a mysql table to excel. I do a website for a friend and have been exporting it myself and emailing it to him. However, I thought it'd be easier if I could just direct him to a link so he could get it whenever.

 

Anyway, the code below works pretty well...the only problem being that it exports all data to the first column of the excel file. There are commas between all fields, so it is possible to use the 'text to columns' feature in excel to separate teh data into separate columns.

 

However, if there is a way of fixing the code that would negate the need to have to do this, it would be much appreciated.

 

Here goes:

 

 

<?php

$db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD");
mysql_select_db("MYDATABASE",$db);

$query="SELECT * FROM MYTABLENAME";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$csv_output = "firstname,lastname,emailaddress,crew_name_001"; 
$csv_output .= "\r\n"; 

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");

$firstname = mysql_result($result,$i,"firstname");
$lastname = mysql_result($result,$i,"lastname");
$emailaddress = mysql_result($result,$i,"emailaddress");
$crew_name_001 = mysql_result($result,$i,"crew_name_001");

$csv_output .= "$firstname,$lastname,$emailaddress,$crew_name_001\n";
++$i;
}

$mode="xls";
$type="excel";


header("Content-type: text/x-csv");  

header("Content-disposition: attachment; filename=".date("d-m-Y")."-export.xls");  

print $csv_output;
exit;

?>

 

Thanks for your time,

 

Dave

[harristweed]

$db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD");
mysql_select_db("MYDATABASE",$db);

$query="SELECT * FROM MYTABLENAME";
$result=mysql_query($query);

$header="firstname\t lastname\t emailaddress\t crew_name_001\t ";
while($row = mysql_fetch_assoc($result)) {
    if(empty($row[firstname]))$row[firstname]=" ";
    if(empty($row[lastname]))$row[lastname]=" ";
    if(empty($row[emailaddress]))$row[emailaddress]=" ";
    if(empty($row[crew_name_001]))$row[crew_name_001]=" ";
    $line = ''; 
    $line .= "$row[firstname]\t $row[lastname]\t $row[emailaddress]\t $row[crew_name_001]\t "; 
    $data .= trim($line)."\n"; 
} 
$data = str_replace("\r","",$data); 
if ($data == "") { 
    $data = "\n(0) Records Found!\n";                         
} 

header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=filename=".date("d-m-Y")."-export.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data";
exit;

 

[davidcriniti]

Worked perfectly. Thanks harristweed !