Loop through to get total for Monday-Sunday records

I want to display all records for a month that fits my location criteria. Currently i am able to display all records but i also need a total row for every record that falls in the week of Monday-Sunday, for example for 6/1-6/30 i would want to display a total row after 6/5, 6/12, 6/19, 6/26


$query = "SELECT weekday(ddate) as wkday, DATE_FORMAT(daily.ddate,'%m/%d/%Y'), daily.day, daily.sales_count, daily.car_count, locations.location, salesman.name 
          FROM daily 
          LEFT JOIN locations ON (locations.id=daily.location)
		  LEFT JOIN salesman ON (salesman.id=daily.salesman)
		  WHERE daily.location='$_POST[location]'
		  GROUP BY daily.ddate";

$result = mysql_query($query) or die("Cannot perform query: ". mysql_error());

?>
<html>
   <head>
	 		<title>Conversion Chart</title>
   </head>
<body>
  <form name="form1" id="form1" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <strong> 
    <label> 
    <select id="location" name="location"   size="1" style="border:solid 1px #999999; font-size:12px; ">

                      <option value="">Selecton One</option>

                     

                      <?

               $sQuery = mysql_query("SELECT id, location FROM locations WHERE active='1' ORDER BY location ASC");  

           while ($sRow = mysql_fetch_row($sQuery))
                    {

                                                   print ("<OPTION value='$sRow[0]'>$sRow[1]</OPTION>");
												 
                    }
?>

              </select>
              <? echo $sRow[1]; ?>
    <input type="submit" class="noprint" name="submit" value="Update" />

  </form>
 
   <fieldset>
      <legend><?=$status?></legend>
      <strong><font size="5"> <font color="#FFFF00">Conversion Report for  </font></font></strong><font color="#FFFF00"><? echo $sRow[1];?><BR>

<table border=0 cellspacing=1 cellpadding=4 bgcolor=#cccccc>
      <tr>
		<td class=header width=10%>Day </a></td>
         <td class=header width=5%>Date</a></td>
<td class=header width=3%>Tickets</td>
<td class=header width=4%>Car Count</td>
<td class=header width=4%>Jobs Total</td>
<td class=header width=4%>Car Total</td>
<td class=header width=6%>Conversion</td>
          </tr>
<?
      $i = 0;

      while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result))
      {

         $i++;

         if(fmod($i, 2) == 0)
		 //if (floor($i/2) * 2 != $i)
         {
            echo "<tr bgcolor=#F5F5F5>";
         }
         else
         {
            echo "<tr bgcolor=#FFFFFF>";
         }

			
?>
		<td><?=$day?></td>
		<td><?=$ddate?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=round($sales_count/$car_count,2)?></td></tr>

	 <?

}
      mysql_free_result($result);
	//  mysql_free_result($result1);
?>
      </table>
	   
   </fieldset>

Calculate your totals in the loop, and display them after each sunday:


// initialize totals
$sales_total = 0;
$car_total = 0;

while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result)) {
  $i++;

  // add to totals
  $sales_total += $sales_count;
  $car_total += $car_count;

  if (fmod($i, 2) == 0) {
    echo "<tr bgcolor=#F5F5F5>";
  } else {
    echo "<tr bgcolor=#FFFFFF>";
  }
?>
		<td><?=$day?></td>
		<td><?=$ddate?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=round($sales_count/$car_count,2)?></td></tr>

<?php
  // if you just displayed a sunday, show the totals
  if ($day = 6) {
    // show totals
    ...

    // initialize totals for a new week
    $sales_total = 0;
    $car_total = 0;
  }   
}

hi,

i tried the code you posted, but now it’s adding a total row after every day not only sunday

Oops…

if ($day == 6)

thanks!

quick question…how would i code it where if it doesn’t have a record for sunday it would still give a total if the date still falls in between Monday-Sunday for the last record?

Using guido’s code:

<?php
// initialize totals
$sales_total = 0;
$car_total = 0;
$curdate = 1; 
while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result)) {
  $i++;
   if ($day <= $curdate) {
    // show totals
    ...
 
    // initialize totals for a new week
    $sales_total = 0;
    $car_total = 0;
  }
  // add to totals
  $sales_total += $sales_count;
  $car_total += $car_count;
 
  //Modify $curdate
  $curdate = $day;

  if (fmod($i, 2) == 0) {
    echo "<tr bgcolor=#F5F5F5>";
  } else {
    echo "<tr bgcolor=#FFFFFF>";
  }
?>
        <td><?=$day?></td>
        <td><?=$ddate?></td>
        <td><?=$sales_count?></td>
        <td><?=$car_count?></td>
        <td><?=$sales_count?></td>
        <td><?=$car_count?></td>
        <td><?=round($sales_count/$car_count,2)?></td></tr>
<?php
}

Now; this will screw up if you… for example, have records for Monday-Wed on week 1, and then nothing until Thurs on week 2. How often will you be missing records?

i think it’s possible for a location to be missing records often, if they are closed due to rainy weather.

mkay…little more complicated then…

$curdate = 1; 
while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result)) {
  $i++;
$weekfirst = strtotime($ddate) - (($day - 1) * 86400); //The timestamp of Monday of this week. 
   if ($weekfirst != $curdate) {
     if($curdate != 1) {
        //Display totals
     }
        $curdate = $weekfirst;
     }
   }

This script will however screw up if you have data from 1970. :stuck_out_tongue:

okay…i’ve implemented the code and it’s showing a total row for each day.


     // initialize totals
$sales_total = 0;
$car_total = 0;
$curdate = 1; 
      //$i = 0;

      while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result))
      {

       //  $i++;
		  // add to totals
  $sales_total += $sales_count;
  $car_total += $car_count;
  $gcar_total += $car_count;
  $gsales_total += $sales_count;
  $gjobs_total += $sales_count;
  //$gcar_conv +=
?>
		<td><?=$day?></td>
		<td><?=$ddate?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=round($sales_count/$car_count,2)?></td></tr>
		<?php
  // if you just displayed a sunday, show the totals
    $weekfirst = strtotime($ddate) - (($day - 1) * 86400); //The timestamp of Monday of this week. 
   if ($weekfirst != $curdate) {
     if($curdate != 1) {
    // show totals
   echo "<tr bgcolor='red'><td colspan='2' align='right'>Total</td><td>$sales_total<td><td>$car_total</td><td colspan=3></td></tr>";
 }
  $curdate = $weekfirst;
    // initialize totals for a new week
    $sales_total = 0;
    $car_total = 0;
  }    

}

does it make a difference if $day = ‘Sunday’ ?

okay…i’m almost there…instead of display the total row after sunday it is displaying it after monday.

after reviewing the results further i realized that it’s not giving the right results. For example if i run a report for a location for the month of june and they have records for:
06/08
06/11
06/12
//i would want a total row here…

06/13
06/14
//another total row here even though it’s not the end of the week yet.

I would like a total row anytime a record falls between monday-sunday of a week.

Can you post the code you are using now? And the result it gives you?

Well you didnt put my if condition where i showed you to put it, so yes, you’re getting it after a monday. Put it at the top of the while like i showed you, and you should get the desired result.

You’ll also need to display the last total-row after the while loop ends.


 <?
      // initialize totals
$sales_total = 0;
$car_total = 0;
$curdate = 1; 
      $i = 0;
      
      while(list($wkday, $ddate, $day, $sales_count, $car_count, $location, $name) = mysql_fetch_row($result))
      {
       if ($wkday <= $curdate) {
         $i++;
		  // add to totals
  $sales_total += $sales_count;
  $car_total += $car_count;
  $gcar_total += $car_count;
  $gsales_total += $sales_count;
  $gjobs_total += $sales_count;
  
  
   }
   
?>
		<td><?=$day?></td>
		<td><?=$ddate?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=$sales_count?></td>
		<td><?=$car_count?></td>
		<td><?=round($sales_count/$car_count,2)?></td></tr>
		<?php
  // if you just displayed a sunday, show the totals
    $weekfirst = strtotime($ddate) - (($wkday-1) * 86400); //The timestamp of Monday of this week.
	//echo $weekfirst;
   if ($weekfirst != $curdate) {
     if($curdate != 1) {
     // show totals
   echo "<tr bgcolor='red'><td colspan='2' align='right'>Total</td><td>$sales_total</td><td>$car_total</td><td colspan=3></td></tr>";
 }
  $curdate = $weekfirst;
  
   
  }    
    
}
// initialize totals for a new week
    $sales_total = 0;
    $car_total = 0;
	
	
      mysql_free_result($result);
?>
<tr><td colspan="2"><td>Tix Ttl</td><td>Car Count Conv.</td><td>Jobs Ttl</td><td>Cars TTL</td><td>Conv. TTL</td></tr>
<tr><td colspan="2"><td><?=$gsales_total?></td><td><?= round($gsales_total/$gcar_total,2)?></td><td><?=$gjobs_total?></td><td><?=$gcar_total?></td><td><?= round($gjobs_total/$gcar_total,2)?></td></tr>
      </table>
	   
   </fieldset>
 

locations with records on sunday and monday results are as shown:
Wednesday 06/18
Saturday 06/11
Sunday 06/12
Monday 06/13
//total row (displayed after monday instead of sunday)

locations with out monday or sunday records results are as follow:
Tuesday 06/07
Tuesday 06/14
//total row displayed (should be displayed after 06/07 since there is only record for this week)

Again. Move the whole section of code i gave you to the top of the while loop where i put it, and it will be correct. You’ve left it at the bottom.

ahh…it’s working now…thanks for all the help.