MySQL to Excel with PHP

[cmaclennan]

Hey Guys, hoping someone can tell me what i'm missing or where im going wrong with my code im trying to return data from multiple tables into an excel sheet, here is the code I have so far that I have found online and tweaked a little but all I get in the file is the last colum header and number.

 

Thanks in advance.

 

<?php


include('mysql_connect.php');
$result = mysql_query('SELECT customers_log.*, shipping.*, orders.*, parts_ordered.* FROM customers_log, shipping, orders, parts_ordered WHERE customers_log.customer_id = shipping.customer_id AND shipping.shipping_id = orders.shipping_id AND orders.order_id = parts_ordered.order_id
');
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
$header = mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}


$line = $value;
}
$data = trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-Type: application/vnd.ms-excel; name='excel'");

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=orderlog.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 

//print "done";

?>

[J.Daniels]

I think the $data variable is getting overwritten. change

 

$data = trim($line)."\n";

 

to

 

$data .= trim($line)."\n";

[gevans]

for ($i = 0; $i < $count; $i++){
$header = mysql_field_name($result, $i)."\t";
}

 

That loop is just assigning a new variable to $header x amount of times.