I have a page that displays the query results in a table. I also added some code to open the table in an Excel spreadsheet. The problem that I'm having is that if I add a where clause to the query, the data will not open in the excel spreadsheet. The parameters for the where clause come from a previous table. Here's the code, it's quite long, so please bear with me:
<?php
include '../mysql_connect.php';
//PERIOD NAME
if (isset($_GET["periodname"]
))
$periodName = $_GET["periodname"];
//PERIOD START DATE
if (isset($_GET["pstartdate"]
))
$pstartDate = $_GET["pstartdate"];
//PERIOD END DATE
if (isset($_GET["penddate"]))
$pendDate = $_GET["penddate"];
$data="
<html>
<head>
</head>
<body>
<table class=\"rpt\" width=\"100%\" border=\"0\">
<tr>
<th class=\"rpt\"> </th>
<th colspan=\"14\" class=\"rpt\">NV & West</th>
<th colspan=\"5\" class=\"rpt\">Total Remakes</th>
</tr>
<tr>
<th width=\"6%\" class=\"rpt\">Date</th>
<th width=\"5%\" class=\"rpt\">Printed</th>
<th class=\"rpt\">Total Pages</th>
<th class=\"rpt\">Last Copy</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">Last Plate</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">Press Start</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">First Bndl</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">Press Stop</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">Last Bndl</th>
<th class=\"rpt\">Avg Min Late</th>
<th class=\"rpt\">Prod</th>
<th class=\"rpt\">New News</th>
<th class=\"rpt\">Cosmetic</th
>
<th class=\"rpt\">Total</th>
<th class=\"rpt\">% of Run</th>
</tr>";
$query="SELECT date_format(rpt_date,'%a %e-%b'), sorc, cmechctrstp, cmechctrstr, pages, g_clstcpy, clstcpy, ".
"g_clstpte, clstpte, g_cprsstr, cprsstr, g_cfstbndl, cfstbndl, g_cprsstp, cprsstp, g_clstbndl, clstbndl, ".
"fprd, fedn, fedc, date_format(rpt_date,'%w')
".
"FROM production_rpt ".
//"WHERE DATE(rpt_date)>='$pstartDa
te' AND DATE(rpt_date)<='$pendDate
' ".
"ORDER BY rpt_date;";
$result = mysql_query($query);
while(($row = mysql_fetch_array($result)
) != false)
{
$rpt_date=$row[0];
$sorc=$row[1];
$cmechctrstp=$row[2];
$cmechctrstr=$row[3];
$pages=$row[4];
$g_clstcpy=$row[5];
$clstcpy=$row[6];
$g_clstpte=$row[7];
$clstpte=$row[8];
$g_cprsstr=$row[9];
$cprsstr=$row[10];
$g_cfstbndl=$row[11];
$cfstbndl=$row[12];
$g_cprsstp=$row[13];
$cprsstp=$row[14];
$g_clstbndl=$row[15];
$clstbndl=$row[16];
$fprd=$row[17];
$fedn=$row[18];
$fedc=$row[19];
$day_num=$row[20];
if($sorc='C')
{
$printed = number_format(($cmechctrst
p - $cmechctrstr)/2,0,'.',',')
;
} else {
$printed = number_format($cmechctrstp
- $cmechctrstr,0,'.',',');
}
if ($clstcpy>$g_clstcpy)
{
$mv_clstcpy=get_time_diff(
$g_clstcpy
, $clstcpy);
} else {
$mv_clstcpy='';
}
if ($clstpte>$g_clstpte)
{
$mv_clstpte=get_time_diff(
$g_clstpte
, $clstpte);
} else {
$mv_clstpte='';
}
if ($cprsstr>$g_cprsstr)
{
$mv_cprsstr=get_time_diff(
$g_cprsstr
, $cprsstr);
} else {
$mv_cprsstr='';
}
if ($cfstbndl>$g_cfstbndl)
{
$mv_cfstbndl=get_time_diff
($g_cfstbn
dl, $cfstbndl);
} else {
$mv_cfstbndl='';
}
if ($cprsstp>$g_cprsstp)
{
$mv_cprsstp=get_time_diff(
$g_cprsstp
, $cprsstp);
} else {
$mv_cprsstp='';
}
if ($clstbndl>$g_clstbndl)
{
$mv_clstbndl=get_time_diff
($g_clstbn
dl, $clstbndl);
} else {
$mv_clstbndl='';
}
$t_r=$fprd+$fedn+$fedc;
$tr_p=number_format($t_r/$
pages*100,
2,'.','').
'%';
$data.="<tr class=\"d".($i & 1)."\">";
$data.="<td>$rpt_date</td>
";
$data.="<td>$printed</td>"
;
$data.="<td>$pages</td>";
$data.="<td>$clstcpy</td>"
;
$data.="<td>$mv_clstcpy</t
d>";
$data.="<td>$clstpte</td>"
;
$data.="<td>$mv_clstpte</t
d>";
$data.="<td>$cprsstr</td>"
;
$data.="<td>$mv_cprsstr</t
d>";
$data.="<td>$cfstbndl</td>
";
$data.="<td>$mv_cfstbndl</
td>";
$data.="<td>$cprsstp</td>"
;
$data.="<td>$mv_cprsstp</t
d>";
$data.="<td>$clstbndl</td>
";
$data.="<td>$mv_clstbndl</
td>";
$data.="<td>$fprd</td>";
$data.="<td>$fedn</td>";
$data.="<td>$fedc</td>";
$data.="<td>$t_r</td>";
$data.="<td>$tr_p</td>";
$data.="</tr>";
$i++;
if ($day_num!=0) {
$total_weekdays++;
} elseif ($day_num==0) {
$total_sun++;
}
} //Closes the while loop
$data.="</table>
{DOWNLOADLINK}
</body>
</html>";
function get_time_diff($time1, $time2)
{
$time_diff = strtotime($time2) - strtotime($time1);
$hours = floor($time_diff / 3600);
$minutes = floor(($time_diff % 3600) / 60);
return(sprintf("%02d:%02d"
, $hours, $minutes));
}
//Following is the place to decide to output as HTML or create the file instead.
//If requested parameter has been sent by the link to this php pave
//then it will set the headers so it will send it as "file" as in my sample
if (isset($_GET['xldownload']
))
{
//Also checking xldownload parameter value to make sure it is set correctly
//Not necessary but good habit to expect a known value instead only checking for the existence
if (intval($_GET['xldownload'
])==1) {
//removing the {DOWNLOADLINK}, because this will not be shown in the HTML page
$data = str_replace("{DOWNLOADLINK
}","",$dat
a);
header('Content-type: application/vnd.ms-excel')
;
header('Content-Dispositio
n: attachment; filename=tabledata.xls');
}
}
else
{
//Sending output as HTML to the client browser
//So we need to put a link for download
//We use the same script name however with xldownload=1 value (check out the href property of the anchor
$data = str_replace("{DOWNLOADLINK
}","<a href=\"prod_mon_rpt_rstls.
php?xldown
load=1\">F
ile</a>",$
data);
}
echo $data;
?>
Start Free Trial
