[RESOLVED] trying to display a list of values by user, with a sum total at bottom - stuck!

[peanutgallery83]

Background:

Have a database that is tracking # of hours that individuals put in on various assignments. I'm attempting to display a report page where all hours worked within a selected date range will display, grouped by the individual:

Example:
JOHN SMITH
03-01-2008 1.5 hours
03-05-2008 5.0 hours

JANE JACKSON
03-01-2008 0.5 hours
03-15-2008 1.5 hours

This part I have working properly.

I'm now trying to add a feature to display the SUM of those selected and displayed hours:

Example:
JOHN SMITH
03-01-2008 1.5 hours
03-05-2008 5.0 hours
Total 6.5 hours

JANE JACKSON
03-01-2008 0.5 hours
03-15-2008 1.5 hours
Total 2.0 hours

I haven't any experience with trying to sum values like this, I've tried playing with the SUM value based on some things I've found on this board, but thus far I've only managed to get errors returned.

The working code is below...any help in getting me in the right direction would be GREATLY appreciated. Thanks in advance.

This is the code I"m using to locate the hours within the specified date range, grouping by name (as shown in my examples above):

$sql = "
SELECT  u.ID,u.First,u.Last,
  p.ID as ProjectID,
  p.Name as ProjectName,
  h.Hours,
  h.Summary,
  h.WorkDate
FROM
  cust_project_hours h
INNER JOIN
  cust_projects p ON p.ID=h.ProjectID
LEFT JOIN
  users u ON u.ID=h.UserID
WHERE
	h.WorkDate BETWEEN date('$_GET[startDate]') AND date('$_GET[endDate]')

ORDER BY
  u.Last,u.First,p.ID

";
$result = $db->Execute($sql) or die($sql."<p>".$db->ErrorMsg());

This is the code that is displaying the information on the page:

print "<table cellspacing=1 cellpadding=2 border=0 width=400>";

$lastProject='';
$lastDeveloper='-';
while($row=$result->FetchRow()) {
  $developer=$row['First'].' '.$row['Last'];
  if($developer==' ')$developer='* UNASSIGNED *';
  $project=$row['ProjectName'];
  if($lastDeveloper!=$developer){
  print "<tr>";
  print "<td colspan=5 style='border-bottom:2px solid #cccccc;'><h5>".htmlentities($developer)."</h5></td>";
  print "</tr>";
  $lastDeveloper=$developer;
 }
  print "<tr>";
  print "<td width=10>&nbsp;</td>";

  print "<td class='smtxt bline' align=left>Updated: ".date('M jS',strtotime($row['WorkDate']))."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hours: ".$row['Hours']."</td>";
  print "</tr>";
}
print "</table>";
print "</div>";

[mikell]

Well one way to do it would be to sum the hours for each developer as you loop through the records.

$developer_total_hours += $row['Hours'];

And of course whenever you get to a new developer, reset it:

$developer_total_hours = 0;

To do the same in SQL is a bit trickier, but you could do it with the following:
group by
sum()
and you may need group_concat(), depending on what fields you want to display.

You can find the definitions for those functions with examples on
http://dev.mysql.com/doc/

[peanutgallery83]

Thanks. I'm getting closer..but am still coming up just a bit short. Hoping someone can help me out here, I'm so close, but just can't seem to get the last bit to work properly!! Grrr!!!

So far I've been able to get this result:

JOHN SMITH
3-01-2008 1.5 Hours 1.5 Hours
3-15-2008 0.25 Hours 1.75 Hours

JANE JOHNSON
3-01-2008 1.5 Hours 1.5 Hours
3-15-2008 2.0 Hours 3.5 Hours

As you can see, I'm getting it to keep a running tally of the total hours for each individual. That's close to what I want, but I cannot seem to figure this out.

What am I missing in order to get that "total_hours" amount into a row beneath the running list of dates/hours.

In other words, I want the above example (which is what I'm getting now), to look like this:

JOHN SMITH
3-01-2008 1.5 Hours
3-15-2008 0.25 Hours
Total Hours: 1.75 Hours

JANE JOHNSON
3-01-2008 1.5 Hours

3-15-2008 2.0 Hours

Total Hours: 3.5 Hours

The code currently looks like this:

$sql = "
SELECT  u.ID,u.First,u.Last,
  p.ID as ProjectID,
  p.Name as ProjectName,
  h.Hours,
  h.Summary,
  h.WorkDate
FROM
  cust_project_hours h
INNER JOIN
  cust_projects p ON p.ID=h.ProjectID
LEFT JOIN
  users u ON u.ID=h.UserID
WHERE
	h.WorkDate BETWEEN date('$_GET[startDate]') AND date('$_GET[endDate]')

ORDER BY
  u.Last,u.First,p.ID

";

Code to display the list of individuals, hours filed, and total hours (not currently working).

$sumTotalHours = 0;
print "<table cellspacing=1 cellpadding=2 border=0 width=400>";

$lastProject='';
$lastDeveloper='-';
while($row=$result->FetchRow()) {

$sumTotalHours += $row['Hours'];

  $developer=$row['First'].' '.$row['Last'];
  if($developer==' ')$developer='* UNASSIGNED *';
  $project=$row['ProjectName'];
  if($lastDeveloper!=$developer){
   $sumIndHours = 0; 

  print "<tr>";
  print "<td colspan=5 style='border-bottom:2px solid #cccccc;'><h5>".htmlentities($developer)."</h5></td>";
  print "</tr>";
  $lastDeveloper=$developer;
 }

 $sumIndHours += $row['Hours'];
  print "<tr>";
  print "<td width=10>&nbsp;</td>";

  print "<td class='smtxt bline' align=left>Updated: ".date('M jS',strtotime($row['WorkDate']))."&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hours: ".$row['Hours']."</td>";
  print "<td>$sumIndHours</td>";
 	print "</tr>";

;
}
  print "<tr>";
  print "<td><br><br><br><br>Total Hours: $sumTotalHours</td>";
  print "</tr>";

  print "</table>";
print "</div>";

Please help, I'm so close, but so stuck!!

Thank you in advance.

[mikell]

Don't expect to be able to just copy and paste this code and have it work.
It might, but I didn't test it. There are plenty of comments so you should
be able to see whats going on and modify it if you need to.

$lastDeveloper = '-';
$sumTotalHours = 0;

echo "<table>";

while($row = $result->FetchRow())
{
  $developer = trim($row['First']).' '.trim($row['Last']);

  //should probably use a ID (auto-inc pk) to identify a developer b/c what if
  //two developers in a row have no name...this code will count them as both as a single
  //developer ($lastDeveloper will be same as $developer when both have no name)
  if($developer == ' ')
  {
    $developer = '* UNASSIGNED *';
  }

  //if processing a new developer
  if($lastDeveloper != $developer)
  {
    //if past first developer, echo the last developers total
    if($lastDeveloper == '-')
    {
      echo "<tr>";
      echo "<td>Total Hours: $sumTotalHours</td>";
      echo "</tr>";
    }

//echo developers name
echo "<tr>";
echo "<td>".htmlentities($developer)."</td>";
echo "</tr>";

//reset $sumTotalHours
$sumTotalHours = 0;

//keep track of which developer we're on
$lastDeveloper = $developer;
  }

  //add hours to this developers total so far
  $sumTotalHours += $row['Hours'];

  //echo info for this developer
  echo "<tr>";
  echo "<td>".date('M jS',strtotime($row['WorkDate']))." Hours ".$row['Hours']."</td>";
  echo "</tr>";
}

echo "</table>";

[peanutgallery83]

Thank you very much. That got me close and was able to tweak it to get it.