Optimizing some procedural code
clintonia
I was charged with creating a statistics report for a client. It
works, but it is VERY slow (it's currently figuring through a little
over a thousand records). Sooo, I was hoping someone could help guide
me in optimizing this or even a different approach. (I'm afraid
placing SQL queries in a while loop is what is doing it, but I'm not
sure how else to go about it). It also seems to break my CSS after it
loads in FireFox.
It needed to search through all shops, determine the number of good
shops, trip-charges and rejected shops by month per client. It also
needed to compare all that to the previous year's totals.
sh = good shop
tr = trip charge
rj = rejected
anything with a p in front refers to a previous year's stat.
thanks a lot if anyone can help.
<?php
session_cache_expire(240);
session_cache_limiter('private_no_expire');
session_start();
set_time_limit(0);
include('lib/authenticate.inc.php');
include ('../lib/db.php');
$accesslevel = $_SESSION['access'];
if(isset($_GET['year']))
{
$timestamp = $_GET['year'];
}else
{
$timestamp = strtotime("now");
}
$prev_year = date("Y", strtotime("-1 Year", $timestamp));
$current_year = date("Y", $timestamp);
$current_year_start = mktime(0,0,0,1,1,$current_year);
$current_year_end = mktime(0,0,0,12,31,$current_year);
$prev_year_start = mktime(0,0,0,1,1,$prev_year);
$prev_year_end = mktime(0,0,0,12,31,$prev_year);
if($_POST['search'])
{
$builder = $_POST['builder'];
$selector = $_POST['selector'];
if(!empty($builder))
{
$search=true;
$build_p = explode("-", $builder);
$b_id = $build_p[0];
$builder_name = $build_p[1];
}else
{
$search = false;
}
if($search)
{
if($selector=="id")
{
$where = "AND users.user_id = '$b_id' ";
}elseif($selector=="bu")
{
$where = "AND users.builder = '$builder_name' ";
}
}else
{
$where = '';
}
}else
{
$where = '';
}
$sql = "SELECT DISTINCT user_id, builder, division FROM users ";
$sql .= "LEFT JOIN shop_orders as so ON so.builder_id = users.user_id
";
$sql .= "LEFT JOIN sales_reps as sr ON sr.order_id = so.order_id ";
$sql .= "WHERE users.account_id = '11' ";
//$sql .= "AND UNIX_TIMESTAMP(sr.date_to_shop) BETWEEN
$current_year_start AND $current_year_end ";
$sql .= $where;
$sql .= "ORDER BY builder ASC, division ASC ";
$result = mysql_query($sql) or die(mysql_error());
$presult = mysql_query($sql) or die(mysql_error());
$num_results = mysql_num_rows($result);
?>
<?php include("lib/header.php"); ?>
<table width="100%" border="0">
<tr>
<td width="150" valign="top">
<?php include("main-nav.php"); ?>
<?php include("statsinc/client_stat_menu.php"); ?>
</td>
<td valign="top">
<div id="content" class="stats">
<h1>Client Stats for <?php echo $current_year; ?></h1>
<?php
$search_sql = "SELECT user_id, builder, division FROM users WHERE
account_id = '11' ORDER BY builder ASC, division ASC ";
$search_result = mysql_query($search_sql);
?>
<fieldset>
<legend>Filter Clients</legend>
<form action="client_stats.php?year=<?php echo $timestamp; ?>"
method="post">
<select name="builder">
<option value="">Search By Builder</option>
<?php
while($ser_row =mysql_fetch_assoc($search_result))
{
$b_id = $ser_row['user_id'];
$sbuilder = $ser_row['builder'];
$sdivision = $ser_row['division'];
$sbuilder = str_replace("&", "&", $sbuilder);
$sdivision = str_replace("&", "&", $sdivision);
echo "<option value=\"$b_id-$sbuilder\">$sbuilder - $sdivision</option>
\n\r";
}
?>
</select>
<input type="radio" name="selector" value="id" /> Specific Builder/
Division <input type="radio" name="selector" value="bu" /> All Related
Builders
<input type="submit" name="search" value="Filter Clients" />
</form>
</fieldset>
<br />
<table width="100%">
<tr><!-- TABLE HEADERS -->
<th rowspan="2" nowrap="nowrap">Builder/Division</th>
<th rowspan="2" nowrap="nowrap">Year Total</th>
<th colspan="3">Jan</th>
<th colspan="3">Feb</th>
<th colspan="3">Mar</th>
<th colspan="3">Apr</th>
<th colspan="3">May</th>
<th colspan="3">Jun</th>
<th colspan="3">Jul</th>
<th colspan="3">Aug</th>
<th colspan="3">Sept</th>
<th colspan="3">Oct</th>
<th colspan="3">Nov</th>
<th colspan="3">Dec</th>
</tr>
<tr class="shoptypeheader"><!-- SHOPS TRIPS REJECTED ROW -->
<td class="firstcol">SH</td><!-- JAN -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- FEB -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
<td class="firstcol">SH</td><!-- MAR -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- APR -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
<td class="firstcol">SH</td><!-- MAY -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- JUN -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
<td class="firstcol">SH</td><!-- JUL -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- AUG -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
<td class="firstcol">SH</td><!-- SEPT -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- OCT -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
<td class="firstcol">SH</td><!-- NOV -->
<td>TR</td>
<td>RJ</td>
<td class="altcol firstcol">SH</td><!-- DEC -->
<td class="altcol">TR</td>
<td class="altcol">RJ</td>
</tr>
<?php
$counter=0;
while($row=mysql_fetch_assoc($result))
{
$id = $row['user_id'];
$builder = $row['builder'];
$division = $row['division'];
///Good Shop Totals//////////////////////////////////////////////////
$sql = "SELECT sr_id FROM sales_reps as sr ";
$sql .= "LEFT JOIN shop_orders as so ON so.order_id = sr.order_id ";
$sql .= "WHERE sr.status = 'Completed' AND so.builder_id='$id' ";
$sql .= "AND UNIX_TIMESTAMP(sr.date_to_shop) BETWEEN
$current_year_start AND $current_year_end ";
$good_shop_results = mysql_query($sql);
$gs_row = mysql_fetch_assoc($good_shop_results);
$num_gs = mysql_num_rows($good_shop_results);
///Good Shop Totals//////////////////////////////////////////////////
///ALL Shops//////////////////////////////////////////////////
$sql = "SELECT sr.sr_id, UNIX_TIMESTAMP(sr.date_to_shop) as dts,
sr.status, ei.shop_result FROM sales_reps as sr ";
$sql .= "LEFT JOIN shop_orders as so ON so.order_id = sr.order_id ";
$sql .= "LEFT JOIN shop_assignments as sa ON sa.sr_id = sr.sr_id ";
$sql .= "LEFT JOIN exit_interviews as ei ON ei.shop_id = sa.shop_id ";
$sql .= "WHERE (sr.status = 'Completed' OR sr.status = 'Rejected') AND
so.builder_id='$id' ";
$sql .= "AND UNIX_TIMESTAMP(sr.date_to_shop) BETWEEN
$current_year_start AND $current_year_end ";
$shop_results = mysql_query($sql) or die(mysql_error());
///Counters///
//January//
$jan_sh = 0;
$jan_tr = 0;
$jan_rj = 0;
//February//
$feb_sh = 0;
$feb_tr = 0;
$feb_rj = 0;
//March//
$mar_sh = 0;
$mar_tr = 0;
$mar_rj = 0;
//April//
$apr_sh = 0;
$apr_tr = 0;
$apr_rj = 0;
//May//
$may_sh = 0;
$may_tr = 0;
$may_rj = 0;
//June//
$jun_sh = 0;
$jun_tr = 0;
$jun_rj = 0;
//July//
$jul_sh = 0;
$jul_tr = 0;
$jul_rj = 0;
//August//
$aug_sh = 0;
$aug_tr = 0;
$aug_rj = 0;
//Sept//
$sept_sh = 0;
$sept_tr = 0;
$sept_rj = 0;
//Oct//
$oct_sh = 0;
$oct_tr = 0;
$oct_rj = 0;
//Nov//
$nov_sh = 0;
$nov_tr = 0;
$nov_rj = 0;
//Dec//
$dec_sh = 0;
$dec_tr = 0;
$dec_rj = 0;
//End Counters///////////////////////////////////////////////
while($s_row = mysql_fetch_assoc($shop_results))
{
$sr_status = $s_row['status'];
$exit_status = $s_row['shop_result'];
$date = $s_row['dts'];
$month = date("m", $date);
if($sr_status == "Completed")
{
switch($month)
{
case "01":
$jan_sh++;
break;
case "02":
$feb_sh++;
break;
case "03":
$mar_sh++;
break;
case "04":
$apr_sh++;
break;
case "05":
$may_sh++;
break;
case "06":
$jun_sh++;
break;
case "07":
$jul_sh++;
break;
case "08":
$aug_sh++;
break;
case "09":
$sept_sh++;
break;
case "10":
$oct_sh++;
break;
case "11":
$nov_sh++;
break;
case "12":
$dec_sh++;
break;
}
}
if($sr_status == "Rejected")
{
if($exit_status == "Trip x1" || $exit_status =="Trip x2" ||
$exit_status =="Non-Reg Trip x1" || $exit_status =="Non-Reg Trip x2")
{
switch($month)
{
case "01":
$jan_tr++;
break;
case "02":
$feb_tr++;
break;
case "03":
$mar_tr++;
break;
case "04":
$apr_tr++;
break;
case "05":
$may_tr++;
break;
case "06":
$jun_tr++;
break;
case "07":
$jul_tr++;
break;
case "08":
$aug_tr++;
break;
case "09":
$sept_tr++;
break;
case "10":
$oct_sh++;
break;
case "11":
$nov_tr++;
break;
case "12":
$dec_tr++;
break;
}
}//End if trip charge
if($exit_status != "Trip x1" && $exit_status !="Trip x2" &&
$exit_status !="Non-Reg Trip x1" && $exit_status !="Non-Reg Trip x2")
{
switch($month)
{
case "01":
$jan_rj++;
break;
case "02":
$feb_rj++;
break;
case "03":
$mar_rj++;
break;
case "04":
$apr_rj++;
break;
case "05":
$may_rj++;
break;
case "06":
$jun_rj++;
break;
case "07":
$jul_rj++;
break;
case "08":
$aug_rj++;
break;
case "09":
$sept_rj++;
break;
case "10":
$oct_rj++;
break;
case "11":
$nov_rj++;
break;
case "12":
$dec_rj++;
break;
}
}//End if Rejected only
}
}
///ALL Shops//////////////////////////////////////////////////
$class = $counter%2 == 0 ? '' : 'class="altrow"';
?>
<tr <?php echo $class;?>>
<td><?php echo $builder." - ".$division; ?></td>
<td style="border-right: 1px solid #580202;border-left: 1px solid
#580202;"><?php echo $num_gs; ?></td>
<!-- SHOPS TRIPS REJECTED ROW -->
<td class="firstcol blackborder"><?php echo $jan_sh; ?></td><!-- JAN --
>
<td class="blackborder"><?php echo $jan_tr; ?></td>
<td><?php echo $jan_rj; ?></td>
<td class="firstcol blackborder"><?php echo $feb_sh; ?></td><!-- FEB --
>
<td class="blackborder"><?php echo $feb_tr; ?></td>
<td><?php echo $feb_rj; ?></td>
<td class="firstcol blackborder"><?php echo $mar_sh; ?></td><!-- MAR --
>
<td class="blackborder"><?php echo $mar_tr; ?></td>
<td><?php echo $mar_rj; ?></td>
<td class="firstcol blackborder"><?php echo $apr_sh; ?></td><!-- APR --
>
<td class="blackborder"><?php echo $apr_tr; ?></td>
<td><?php echo $apr_rj; ?></td>
<td class="firstcol blackborder"><?php echo $may_sh; ?></td><!-- MAY --
>
<td class="blackborder"><?php echo $may_tr; ?></td>
<td><?php echo $may_rj; ?></td>
<td class="firstcol blackborder"><?php echo $jun_sh; ?></td><!-- JUN --
>
<td class="blackborder"><?php echo $jun_tr; ?></td>
<td><?php echo $jun_rj; ?></td>
<td class="firstcol blackborder"><?php echo $jul_sh; ?></td><!-- JUL --
>
<td class="blackborder"><?php echo $jul_tr; ?></td>
<td><?php echo $jul_rj; ?></td>
<td class="firstcol blackborder"><?php echo $aug_sh; ?></td><!-- AUG --
>
<td class="blackborder"><?php echo $aug_tr; ?></td>
<td><?php echo $aug_rj; ?></td>
<td class="firstcol blackborder"><?php echo $sept_sh; ?></td><!-- SEPT
-->
<td class="blackborder"><?php echo $sept_tr; ?></td>
<td><?php echo $sept_rj; ?></td>
<td class="firstcol blackborder"><?php echo $oct_sh; ?></td><!-- OCT --
>
<td class="blackborder"><?php echo $oct_tr; ?></td>
<td><?php echo $oct_rj; ?></td>
<td class="firstcol blackborder"><?php echo $nov_sh; ?></td><!-- NOV --
>
<td class="blackborder"><?php echo $nov_tr; ?></td>
<td><?php echo $nov_rj; ?></td>
<td class="firstcol blackborder"><?php echo $dec_sh; ?></td><!-- DEC --
>
<td class="blackborder"><?php echo $dec_tr; ?></td>
<td><?php echo $dec_rj; ?></td>
</tr>
<?php
$counter++;
$year_total += $num_gs;
//Good shop totals per month
$jan_sh_total += $jan_sh;
$feb_sh_total += $feb_sh;
$mar_sh_total += $mar_sh;
$apr_sh_total += $apr_sh;
$may_sh_total += $may_sh;
$jun_sh_total += $jun_sh;
$jul_sh_total += $jul_sh;
$aug_sh_total += $aug_sh;
$sept_sh_total += $sept_sh;
$oct_sh_total += $oct_sh;
$nov_sh_total += $nov_sh;
$dec_sh_total += $dec_sh;
//Trip totals per month
$jan_tr_total += $jan_tr;
$feb_tr_total += $feb_tr;
$mar_tr_total += $mar_tr;
$apr_tr_total += $apr_tr;
$may_tr_total += $may_tr;
$jun_tr_total += $jun_tr;
$jul_tr_total += $jul_tr;
$aug_tr_total += $aug_tr;
$sept_tr_total += $sept_tr;
$oct_tr_total += $oct_tr;
$nov_tr_total += $nov_tr;
$dec_tr_total += $dec_tr;
//Reject totals per month
$jan_rj_total += $jan_rj;
$feb_rj_total += $feb_rj;
$mar_rj_total += $mar_rj;
$apr_rj_total += $apr_rj;
$may_rj_total += $may_rj;
$jun_rj_total += $jun_rj;
$jul_rj_total += $jul_rj;
$aug_rj_total += $aug_rj;
$sept_rj_total += $sept_rj;
$oct_rj_total += $oct_rj;
$nov_rj_total += $nov_rj;
$dec_rj_total += $dec_rj;
}
//Total Attempts
$jan_totals = $jan_sh_total+$jan_tr_total+$jan_rj_total;
$feb_totals = $feb_sh_total+$feb_tr_total+$feb_rj_total;
$mar_totals = $mar_sh_total+$mar_tr_total+$mar_rj_total;
$apr_totals = $apr_sh_total+$apr_tr_total+$apr_rj_total;
$may_totals = $may_sh_total+$may_tr_total+$may_rj_total;
$jun_totals = $jun_sh_total+$jun_tr_total+$jun_rj_total;
$jul_totals = $jul_sh_total+$jul_tr_total+$jul_rj_total;
$aug_totals = $aug_sh_total+$aug_tr_total+$aug_rj_total;
$sept_totals = $sept_sh_total+$sept_tr_total+$sept_rj_total;
$oct_totals = $oct_sh_total+$oct_tr_total+$oct_rj_total;
$nov_totals = $nov_sh_total+$nov_tr_total+$nov_rj_total;
$dec_totals = $dec_sh_total+$dec_tr_total+$dec_rj_total;
?>
<tr class="stat_totals">
<th><?php echo $current_year; ?> Total</th>
<td class="firstcol"><?php echo $year_total; ?></td>
<!-- SHOPS TRIPS REJECTED TOTALS ROW -->
<td class="firstcol"><?php echo $jan_sh_total; ?></td><!-- JAN -->
<td><?php echo $jan_tr_total; ?></td>
<td><?php echo $jan_rj_total; ?></td>
<td class="firstcol"><?php echo $feb_sh_total; ?></td><!-- FEB -->
<td><?php echo $feb_tr_total; ?></td>
<td><?php echo $feb_rj_total; ?></td>
<td class="firstcol"><?php echo $mar_sh_total; ?></td><!-- MAR -->
<td><?php echo $mar_tr_total; ?></td>
<td><?php echo $mar_rj_total; ?></td>
<td class="firstcol"><?php echo $apr_sh_total; ?></td><!-- APR -->
<td><?php echo $apr_tr_total; ?></td>
<td><?php echo $apr_rj_total; ?></td>
<td class="firstcol"><?php echo $may_sh_total; ?></td><!-- MAY -->
<td><?php echo $may_tr_total; ?></td>
<td><?php echo $may_rj_total; ?></td>
<td class="firstcol"><?php echo $jun_sh_total; ?></td><!-- JUN -->
<td><?php echo $jun_tr_total; ?></td>
<td><?php echo $jun_rj_total; ?></td>
<td class="firstcol"><?php echo $jul_sh_total; ?></td><!-- JUL -->
<td><?php echo $jul_tr_total; ?></td>
<td><?php echo $jul_rj_total; ?></td>
<td class="firstcol"><?php echo $aug_sh_total; ?></td><!-- AUG -->
<td><?php echo $aug_tr_total; ?></td>
<td><?php echo $aug_rj_total; ?></td>
<td class="firstcol"><?php echo $sept_sh_total; ?></td><!-- SEPT -->
<td><?php echo $sept_tr_total; ?></td>
<td><?php echo $sept_rj_total; ?></td>
<td class="firstcol"><?php echo $oct_sh_total; ?></td><!-- OCT -->
<td><?php echo $oct_tr_total; ?></td>
<td><?php echo $oct_rj_total; ?></td>
<td class="firstcol"><?php echo $nov_sh_total; ?></td><!-- NOV -->
<td><?php echo $nov_tr_total; ?></td>
<td><?php echo $nov_rj_total; ?></td>
<td class="firstcol"><?php echo $dec_sh_total; ?></td><!-- DEC -->
<td><?php echo $dec_tr_total; ?></td>
<td><?php echo $dec_rj_total; ?></td>
</tr>
<tr>
<td colspan="38"> </td>
</tr>
<?php
include 'statsinc/prev_year.inc.php';
?>
<tr class="monthly_totals"><!-- MONTHLY TOTALS -->
<th rowspan="3">Monthly Shop Totals</th>
<th><?php echo $current_year; ?> Totals</th>
<td class="firstcol" colspan="3"><?php echo $jan_sh_total; ?></td><!--
Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $feb_sh_total; ?></td><!--
Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $mar_sh_total; ?></td><!--
Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $apr_sh_total; ?></td><!--
Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $may_sh_total; ?></td><!--
May Totals -->
<td class="firstcol" colspan="3"><?php echo $jun_sh_total; ?></td><!--
Jun Totals -->
<td class="firstcol" colspan="3"><?php echo $jul_sh_total; ?></td><!--
Jul Totals -->
<td class="firstcol" colspan="3"><?php echo $aug_sh_total; ?></td><!--
Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $sept_sh_total; ?></
td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $oct_sh_total; ?></td><!--
Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $nov_sh_total; ?></td><!--
Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $dec_sh_total; ?></td><!--
Dec Totals -->
</tr>
<tr class="altrow monthly_totals"><!-- PREV YEAR MONTHLY TOTALS -->
<th><?php echo $prev_year; ?> Totals</th>
<td class="firstcol" colspan="3"><?php echo $pjan_sh; ?></td><!-- Jan
Totals -->
<td class="firstcol" colspan="3"><?php echo $pfeb_sh; ?></td><!-- Feb
Totals -->
<td class="firstcol" colspan="3"><?php echo $pmar_sh; ?></td><!-- Mar
Totals -->
<td class="firstcol" colspan="3"><?php echo $papr_sh; ?></td><!-- Apr
Totals -->
<td class="firstcol" colspan="3"><?php echo $pmay_sh; ?></td><!-- May
Totals -->
<td class="firstcol" colspan="3"><?php echo $pjun_sh; ?></td><!-- Jun
Totals -->
<td class="firstcol" colspan="3"><?php echo $pjul_sh; ?></td><!-- Jul
Totals -->
<td class="firstcol" colspan="3"><?php echo $paug_sh; ?></td><!-- Aug
Totals -->
<td class="firstcol" colspan="3"><?php echo $psept_sh; ?></td><!--
Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $poct_sh; ?></td><!-- Oct
Totals -->
<td class="firstcol" colspan="3"><?php echo $pnov_sh; ?></td><!-- Nov
Totals -->
<td class="firstcol" colspan="3"><?php echo $pdec_sh; ?></td><!-- Dec
Totals -->
</tr>
<tr class="monthly_totals"><!-- Month +/- -->
<th>Month +/-</th>
<td class="firstcol" colspan="3"><?php echo
@number_format((($jan_sh_total-$pjan_sh)/$pjan_sh)*100, 2) ?>%</
td><!-- Jan Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($feb_sh_total-$pfeb_sh)/$pfeb_sh)*100, 2) ?>%</
td><!-- Feb Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($mar_sh_total-$pmar_sh)/$pmar_sh)*100, 2) ?>%</
td><!-- Mar Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($apr_sh_total-$papr_sh)/$papr_sh)*100, 2) ?>%</
td><!-- Apr Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($may_sh_total-$pmay_sh)/$pmay_sh)*100, 2) ?>%</
td><!-- May Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($jun_sh_total-$pjun_sh)/$pjun_sh)*100, 2) ?>%</
td><!-- Jun Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($jul_sh_total-$pjul_sh)/$pjul_sh)*100, 2) ?>%</
td><!-- Jul Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($aug_sh_total-$paug_sh)/$paug_sh)*100, 2) ?>%</
td><!-- Aug Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($sept_sh_total-$psept_sh)/$psept_sh)*100, 2) ?>%</
td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($oct_sh_total-$poct_sh)/$poct_sh)*100, 2) ?>%</
td><!-- Oct Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($nov_sh_total-$pnov_sh)/$pnov_sh)*100, 2) ?>%</
td><!-- Nov Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format((($dec_sh_total-$pdec_sh)/$pdec_sh)*100, 2) ?>%</
td><!-- Dec Totals -->
</tr>
<tr>
<td colspan="38"> </td>
</tr>
<!-- YTD TOTALS -->
<tr class="ytd_totals"><!-- MONTHLY TOTALS -->
<th rowspan="3">YTD Totals</th>
<th><?php echo $current_year; ?> YTD</th>
<td class="firstcol" colspan="3"><?php echo $ytd_jan = $jan_sh_total; ?
></td><!-- Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_feb = $feb_sh_total+
$jan_sh_total; ?></td><!-- Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_mar = $mar_sh_total+
$feb_sh_total+$jan_sh_total; ?></td><!-- Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_apr = $apr_sh_total+
$mar_sh_total+$feb_sh_total+$jan_sh_total; ?></td><!-- Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_may = $may_sh_total+
$apr_sh_total+$mar_sh_total+$feb_sh_total+$jan_sh_total; ?></td><!--
May Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_jun = $jun_sh_total+
$may_sh_total+$apr_sh_total+$mar_sh_total+$feb_sh_total+
$jan_sh_total; ?></td><!-- Jun Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_jul = $jul_sh_total+
$jun_sh_total+$may_sh_total+$apr_sh_total+$mar_sh_total+$feb_sh_total+
$jan_sh_total; ?></td><!-- Jul Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_aug = $aug_sh_total+
$jul_sh_total+$jun_sh_total+$may_sh_total+$apr_sh_total+$mar_sh_total+
$feb_sh_total+$jan_sh_total; ?></td><!-- Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_sept = $sept_sh_total+
$aug_sh_total+$jul_sh_total+$jun_sh_total+$may_sh_total+$apr_sh_total+
$mar_sh_total+$feb_sh_total+$jan_sh_total; ?></td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_oct = $oct_sh_total+
$sept_sh_total+$aug_sh_total+$jul_sh_total+$jun_sh_total+$may_sh_total+
$apr_sh_total+$mar_sh_total+$feb_sh_total+$jan_sh_total; ?></td><!--
Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_nov = $nov_sh_total+
$oct_sh_total+$sept_sh_total+$aug_sh_total+$jul_sh_total+$jun_sh_total+
$may_sh_total+$apr_sh_total+$mar_sh_total+$feb_sh_total+
$jan_sh_total; ?></td><!-- Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_dec = $dec_sh_total+
$nov_sh_total+$oct_sh_total+$sept_sh_total+$aug_sh_total+$jul_sh_total+
$jun_sh_total+$may_sh_total+$apr_sh_total+$mar_sh_total+$feb_sh_total+
$jan_sh_total; ?></td><!-- Dec Totals -->
</tr>
<tr class="altrow ytd_totals"><!-- PREV YEAR MONTHLY TOTALS -->
<th><?php echo $prev_year; ?> YTD</th>
<td class="firstcol" colspan="3"><?php echo $ytd_pjan = $pjan_sh; ?></
td><!-- Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pfeb = $pfeb_sh+
$pjan_sh; ?></td><!-- Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pmar = $pmar_sh+
$pfeb_sh+$pjan_sh; ?></td><!-- Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_papr = $papr_sh+
$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pmay = $pmay_sh+
$papr_sh+$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- May Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pjun = $pjun_sh+
$pmay_sh+$papr_sh+$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- Jun Totals --
>
<td class="firstcol" colspan="3"><?php echo $ytd_pjul = $pjul_sh+
$pjun_sh+$pmay_sh+$papr_sh+$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- Jul
Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_paug = $paug_sh+
$pjul_sh+$pjun_sh+$pmay_sh+$papr_sh+$pmar_sh+$pfeb_sh+$pjan_sh; ?></
td><!-- Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_psept = $psept_sh+
$paug_sh+$pjul_sh+$pjun_sh+$pmay_sh+$papr_sh+$pmar_sh+$pfeb_sh+
$pjan_sh; ?></td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_poct = $poct_sh+
$psept_sh+$paug_sh+$pjul_sh+$pjun_sh+$pmay_sh+$papr_sh+$pmar_sh+
$pfeb_sh+$pjan_sh; ?></td><!-- Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pnov = $pnov_sh+
$poct_sh+$psept_sh+$paug_sh+$pjul_sh+$pjun_sh+$pmay_sh+$papr_sh+
$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $ytd_pdec = $pdec_sh+
$pnov_sh+$poct_sh+$psept_sh+$paug_sh+$pjul_sh+$pjun_sh+$pmay_sh+
$papr_sh+$pmar_sh+$pfeb_sh+$pjan_sh; ?></td><!-- Dec Totals -->
</tr>
<tr class="ytd_totals"><!-- Month +/- -->
<th>YTD +/-</th>
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_jan-
$ytd_pjan)/$ytd_pjan)*100, 2) ?>%</td><!-- Jan Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_feb-
$ytd_pfeb)/$ytd_pfeb)*100, 2) ?>%</td><!-- Feb Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_mar-
$ytd_pmar)/$ytd_pmar)*100, 2) ?>%</td><!-- Mar Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_apr-
$ytd_papr)/$ytd_papr)*100, 2) ?>%</td><!-- Apr Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_may-
$ytd_pmay)/$ytd_pmay)*100, 2) ?>%</td><!-- May Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_jun-
$ytd_pjun)/$ytd_pjun)*100, 2) ?>%</td><!-- Jun Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_jul-
$ytd_pjul)/$ytd_pjul)*100, 2) ?>%</td><!-- Jul Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_aug-
$ytd_paug)/$ytd_paug)*100, 2) ?>%</td><!-- Aug Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_sept-
$ytd_psept)/$ytd_psept)*100, 2) ?>%</td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_oct-
$ytd_poct)/$ytd_poct)*100, 2) ?>%</td><!-- Oct Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_nov-
$ytd_pnov)/$ytd_pnov)*100, 2) ?>%</td><!-- Nov Totals -->
<td class="firstcol" colspan="3"><?php echo @number_format((($ytd_dec-
$ytd_pdec)/$ytd_pdec)*100, 2) ?>%</td><!-- Dec Totals -->
</tr>
<tr>
<td colspan="38"> </td>
</tr>
<tr class="rejects"><!-- MONTHLY TOTALS -->
<th rowspan="5">Monthly Statistics</th>
<th>Trip Charges</th>
<td class="firstcol" colspan="3"><?php echo $jan_tr_total; ?></td><!--
Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $feb_tr_total; ?></td><!--
Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $mar_tr_total; ?></td><!--
Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $apr_tr_total; ?></td><!--
Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $may_tr_total; ?></td><!--
May Totals -->
<td class="firstcol" colspan="3"><?php echo $jun_tr_total; ?></td><!--
Jun Totals -->
<td class="firstcol" colspan="3"><?php echo $jul_tr_total; ?></td><!--
Jul Totals -->
<td class="firstcol" colspan="3"><?php echo $aug_tr_total; ?></td><!--
Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $sept_tr_total; ?></
td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $oct_tr_total; ?></td><!--
Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $nov_tr_total; ?></td><!--
Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $dec_tr_total; ?></td><!--
Dec Totals -->
</tr>
<tr class="altrow rejects"><!-- MONTHLY TOTALS -->
<th>% of Attempts</th>
<td class="firstcol" colspan="3"><?php echo
@number_format(($jan_tr_total/$jan_totals)*100,2); ?>%</td><!-- Jan
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($feb_tr_total/$feb_totals)*100,2); ?>%</td><!-- Feb
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($mar_tr_total/$mar_totals)*100,2); ?>%</td><!-- Mar
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($apr_tr_total/$apr_totals)*100,2); ?>%</td><!-- Apr
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($may_tr_total/$may_totals)*100,2); ?>%</td><!-- May
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($jun_tr_total/$jun_totals)*100,2); ?>%</td><!-- Jun
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($jul_tr_total/$jul_totals)*100,2); ?>%</td><!-- Jul
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($aug_tr_total/$aug_totals)*100,2); ?>%</td><!-- Aug
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($sept_tr_total/$sept_totals)*100,2); ?>%</td><!-- Sept
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($oct_tr_total/$oct_totals)*100,2); ?>%</td><!-- Oct
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($nov_tr_total/$nov_totals)*100,2); ?>%</td><!-- Nov
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($dec_tr_total/$dec_totals)*100,2); ?>%</td><!-- Dec
Totals -->
</tr>
<tr class="rejects"><!-- MONTHLY TOTALS -->
<th>Rejects</th>
<td class="firstcol" colspan="3"><?php echo $jan_rj_total; ?></td><!--
Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $feb_rj_total; ?></td><!--
Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $mar_rj_total; ?></td><!--
Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $apr_rj_total; ?></td><!--
Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $may_rj_total; ?></td><!--
May Totals -->
<td class="firstcol" colspan="3"><?php echo $jun_rj_total; ?></td><!--
Jun Totals -->
<td class="firstcol" colspan="3"><?php echo $jul_rj_total; ?></td><!--
Jul Totals -->
<td class="firstcol" colspan="3"><?php echo $aug_rj_total; ?></td><!--
Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $sept_rj_total; ?></
td><!-- Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $oct_rj_total; ?></td><!--
Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $nov_rj_total; ?></td><!--
Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $dec_rj_total; ?></td><!--
Dec Totals -->
</tr>
<tr class="altrow rejects"><!-- % ATTEMPTS -->
<th>% of Attempts</th>
<td class="firstcol" colspan="3"><?php echo
@number_format(($jan_rj_total/$jan_totals)*100,2); ?>%</td><!-- Jan
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($feb_rj_total/$feb_totals)*100,2); ?>%</td><!-- Feb
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($mar_rj_total/$mar_totals)*100,2); ?>%</td><!-- Mar
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($apr_rj_total/$apr_totals)*100,2); ?>%</td><!-- Apr
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($may_rj_total/$may_totals)*100,2); ?>%</td><!-- May
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($jun_rj_total/$jun_totals)*100,2); ?>%</td><!-- Jun
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($jul_rj_total/$jul_totals)*100,2); ?>%</td><!-- Jul
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($aug_rj_total/$aug_totals)*100,2); ?>%</td><!-- Aug
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($sept_rj_total/$sept_totals)*100,2); ?>%</td><!-- Sept
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($oct_rj_total/$oct_totals)*100,2); ?>%</td><!-- Oct
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($nov_rj_total/$nov_totals)*100,2); ?>%</td><!-- Nov
Totals -->
<td class="firstcol" colspan="3"><?php echo
@number_format(($dec_rj_total/$dec_totals)*100,2); ?>%</td><!-- Dec
Totals -->
</tr>
<tr class="rejects"><!-- TOTAL ATTEMPTS -->
<th>Total Attempts</th>
<td class="firstcol" colspan="3"><?php echo $jan_totals; ?></td><!--
Jan Totals -->
<td class="firstcol" colspan="3"><?php echo $feb_totals; ?></td><!--
Feb Totals -->
<td class="firstcol" colspan="3"><?php echo $mar_totals; ?></td><!--
Mar Totals -->
<td class="firstcol" colspan="3"><?php echo $apr_totals; ?></td><!--
Apr Totals -->
<td class="firstcol" colspan="3"><?php echo $may_totals; ?></td><!--
May Totals -->
<td class="firstcol" colspan="3"><?php echo $jun_totals; ?></td><!--
Jun Totals -->
<td class="firstcol" colspan="3"><?php echo $jul_totals; ?></td><!--
Jul Totals -->
<td class="firstcol" colspan="3"><?php echo $aug_totals; ?></td><!--
Aug Totals -->
<td class="firstcol" colspan="3"><?php echo $sept_totals; ?></td><!--
Sept Totals -->
<td class="firstcol" colspan="3"><?php echo $oct_totals; ?></td><!--
Oct Totals -->
<td class="firstcol" colspan="3"><?php echo $nov_totals; ?></td><!--
Nov Totals -->
<td class="firstcol" colspan="3"><?php echo $dec_totals; ?></td><!--
Dec Totals -->
</tr>
</table>
</div>
</td>
</tr>
</table>
<?php include("lib/footer.php"); ?>
Alexander Rudloff
Try to find a way to construct the query in the while loop, but run it after. Then use another loop to process the returned data. If the query is going to run slow, at least this way it'll only run once.
Hope that helps,
Alex Rudloff
$sql .= "WHERE ( sr.status = 'Completed' OR sr.status = 'Rejected') AND
--
http://alex.emurse.com/
http://www.alexrudloff.com/
Mike G.
since there are an infinate number of variables which can be
contributing to your app dragging it's feet.
First thing I would ask is how often are reports generated? If it's
only once a week/month then you may just want to set up a cron job to
have it generate the report for you. I've written a few applications
where the reports were generated and cached as HTML includes. So once
a day a script would run and process data for a few minutes and
afterwards some pretty HTML files were made that when included
appeared instantly.
With that said, if queries on a table of only 1000 records are taking
a long time (how long, by the way?) then you definitely are in serious
need of optimization. Tables with hundreds of thousands of records
should still pop up quickly. Ask yourself is your table structure is
optimized? Is there any way you can partition your table into groups?
Consider one "read only" table for fast searching and a second "write
only" table for data manipulation. How about indexes? Have you set any
index up other than your primary key?
The next approach would be to see just how many queries are taking
place. Set up a $queryCounter variable somewhere in your script and
have it increment with every time you query the database. I usually
get worried once my count gets above 5 or 6 queries. At that point,
you may want to consider a more complex query. You're already using
JOINs so you're on the right track. Don't forget that MySQL now
supports subqueries as well.
Hope this helps!
> <td ...
>
> read more ยป