...

View Full Version : PHP, MySQL and Excel



keith1995
02-15-2008, 07:51 PM
I've been using the below code to grab data from a MySQL database and export it into an Excel xls file. The problem I now am tasked with is that there are two columns at the end of the file that are obsolete to the enduser however are needed to complete the initial query to MySQL (guest_pass.HostingMember and directory.id). I'd like to remove them from the xls on the fly but I can't figure out how to do it.

The code:


$select = "SELECT guest_pass.FirstName AS 'First Name', guest_pass.LastName AS 'Last Name', guest_pass.Address AS 'Street Address', guest_pass.City AS 'City', guest_pass.State AS 'State', guest_pass.Zip AS 'Zip Code', guest_pass.Phone AS 'Phone', guest_pass.Email AS 'Email', guest_pass.ReasonVisit AS 'Reason for Visit', guest_pass.date_full AS 'Date and Time', CONCAT(directory.LastName,', ' , directory.MenFirstName, ' ', directory.WomenFirstName) AS 'Hosting Member', guest_pass.HostingMember, directory.id FROM guest_pass LEFT JOIN directory ON directory.id = guest_pass.HostingMember ORDER BY `date_full` DESC";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

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

while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$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=guest-passes.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

angst
02-15-2008, 07:55 PM
The problem I now am tasked with is that there are two columns at the end of the file that are obsolete to the enduser however are needed to complete the initial query to MySQL (guest_pass.HostingMember and directory.id).


why do you need these columns to complete the query?

Andrew Johnson
02-15-2008, 07:58 PM
Just because you use a column in a JOIN doesn't mean you need to include it in your SELECT.

keith1995
02-15-2008, 08:03 PM
angst and Andrew,

Thanks! I was always under the assumption that columns in a JOIN were needed in the SELECT. I've removed them and all works perfectly!

Thanks for both of your quick responses



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum