[SOLVED] formatting mysql results before exporting as Excel file

[tekrscom]

Hi, I was wondering if anyone might be able to assist me in how to format my MySQL results prior to exporting the data to an Excel file? The table is;

MTES_tech_pay_weeks

W/ Fields;

tech_pay_week_id

tech_pay_week_ending_date

tech_id

tech_pay_week_hours

tech_pay_week_approved

 

What I want to do is, I only want 3 of the 5 fields exported, tech_pay_week_ending_date, tech_id and tech_pay_week_hours. Instead of using the MySQL field names, I'd rather label them in real world name, e.g. Week Ending Date, Technician and Total Hours. Prior to exporting the data though, I need to convert the date to a different date format, e.g. $tech_pay_week_ending_date = date('F j, Y', strtotime("$row[tech_pay_week_ending_date]")); and I need to query a different table to retrieve the name of the tech from his "tech_id", e.g. $query = mysql_query("SELECT fname, lname FROM users WHERE userid = '$row[tech_id]'");

while ($row  =  mysql_fetch_array($query)) {

$name = "$row[fname] $row[lname]";

                    }

 

I followed the tutorial on here on how to export a table to excel and it works great, but I'm just uncertain how to preformat data prior to exporting it. Here is my code;

 

<?php
include_once "../connection.php";

$select = "SELECT * FROM MTES_tech_pay_weeks";

$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=vaspna_time_sheets.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

 

But what I'd like is something like this, only to work...

 

$query = mysql_query("SELECT * FROM MTES_tech_pay_weeks WHERE week_ending_date = '$_POST[week_ending_date]'");
while ($row = mysql_fetch_array($query))	{
	$tech_pay_week_hours = $row[tech_pay_week_hours];
$tech_pay_week_ending_date = date('F j, Y', strtotime("$row[tech_pay_week_ending_date]"));
$query = mysql_query("SELECT fname, lname FROM users WHERE userid = '$row[tech_id]'");
while ($row  =  mysql_fetch_array($query))	{
	$name = "$row[fname] $row[lname]";
                    							}
											}

 

In place of the $select = "SELECT * FROM MTES_tech_pay_weeks"; line.

[GingerRobot]

Hmm, i wonder if something like this:

<?php
include_once "../connection.php";

$select = "SELECT `tech_pay_week_ending_date`, `tech_id` and `tech_pay_week_hours` FROM MTES_tech_pay_weeks WHERE week_ending_date = '$_POST[week_ending_date]'";

$export = mysql_query($select) or die(mysql_error());
//$fields = mysql_num_fields($export);

//for ($i = 0; $i < $fields; $i++) {
//    $header .= mysql_field_name($export, $i) . "\t";
//}
$header = "Week Ending Date \t Technician \t Total Hours \t"; //

while($row = mysql_fetch_assoc($export)){
$line = '';
$query = mysql_query("SELECT fname, lname FROM users WHERE userid = '$row[tech_id]'") or die(mysql_error());
$fname = mysql_result($query,0,'fname');
$lname = mysql_result($query,0,'lname');
$row['tech_id'] = $fname.' '.$lname;
$row['tech_pay_week_ending_date'] = date('F j, Y', strtotime($row[tech_pay_week_ending_date]));
    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";

}

/*
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=vaspna_time_sheets.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

 

Would work. Ive never exported anything to excel, so ive tried to keep it as similar as possible to the original. But it looks to me like the headers was jsut a tab delimited list, so that should be easy enough. The rest is a bit of a guess, but give it a go.

[tekrscom]

It did the titles right and the first column also, but the second column was wrong and the third column was missing.

[GingerRobot]

So it did the week end dates correctly?

The technician column was wrong? what was wrong with it exactly?

And the hours column was completely missing?

[tekrscom]

Yes, the week end dates were correct... the technician column was wrong, because it only listed one particular tech_id, the table actually is currently holding two different tech_id . Yes, the hours column was completly missing.

[tekrscom]

Your code worked, it was just the "and" that you put in your sql query... once I took that out and used a comma, it worked just fine.

[GingerRobot]

Whoops, not quite sure why i put an and in there. Glad to hear it worked.