I checked on the MySQL download page and it's still in Alpha so prehaps it would be better not to upgrade just yet (place a note in the script that when 4.1 reaches stable you can chnage over) and just do the totaling in PHP.
Right, without further ado lets get started 😃
The Query - Only real change is adding in date in the order by
SELECT time.empid AS empid,
emp.name AS empname,
time.project AS project,
project.weight AS weight,
project.description AS description,
date_format(time.tsdate,'%m/%d/%Y') AS date,
UNIX_TIMESTAMP(time.tsdate) AS rawdate,
sum(hours) AS hours
FROM time
INNER JOIN project ON time.project = project.project
INNER JOIN emp ON time.empid = emp.empid
GROUP BY time.project, project.description, time.empid, emp.name, date_format(time.tsdate,'%m/%d/%Y')
ORDER BY project.weight DESC, time.project DESC, time.empid ASC
One thing I would say though is, do we need to be grouping by project.description if we're grouping on the time.project (I'm assuming that's some kind of unique_id) the same goes for emp.name if we're grouping on time.empid.
Now pulling out the data and aggregating. I'm just going to show pulling the data into an array which can then be looped through later. This method is a little slower but it has the advantage of keeping your logic well seperated from your display. If you find the speed becoming an issue the maybe fudge it about a bit.
$sql; //Contains the query
$res; //Contains the MySQL Link
$res=mysql_query($sql,$res) or die('Query error on "'.$sql.'" -- '.mysql_error());
$sub=0; $ttl=0;
$prev_date=null; $prev_proj=null;
$results=array();
for($i=0;$row=mysql_fetch_array($res);$i++) {
if($row['project']!=$prev_proj) {
$results[$i][4]='SUB';
$results[$i][5]=$sub;
$sub=0;
$results[++$i][4]='TTL';
$results[$i][5]=$ttl;
$ttl=0;
$results[++$i][0]=$row['project'];
$results[$i][1]=$row['description'];
$prev_proj=$row['project'];
$prev_date=$row['rawdate'];
} elseif($row['rawdate']!=$prev_date) {
$results[$i][4]='SUB';
$results[$i][5]=$sub;
$sub=0;
$prev_date=$row['rawdate'];
$i++;
$ttl+=$row['hours'];
} else {
$sub+=$row['hours'];
$ttl+=$row['hours'];
}
$results[$i][2]=$row['empname'];
$results[$i][3]=$row['date'];
$results[$i][4]=$row['hours'];
}
You can then display the data later like so
<table class="timedata">
<thead>
<th>ProjectID - ProjectName</th>
<th>Employee</th>
<th>Date</th>
<th> </th>
<th>Hours</th>
</thead>
<?php
$results_c=count($results);
for($i=0;$i<$results_c;$i++) {
?>
<tr>
<td><?php echo($results[$i][0].' - '.$results[$i][1]);?></td>
<td><?php echo($results[$i][2]);?></td>
<td><?php echo($results[$i][3]);?></td>
<td><?php echo($results[$i][4]);?></td>
<td><?php echo($results[$i][5]);?></td>
</tr>
<?php
}
?>
</table>
and that should give you the desired effect.
Let me know if you have any problems with it (like it doesn't work), note that I have not tested it.
HTH
Bubblenut
NB. Not sure if this will help anyone else but I found listening to the Goa/Psy channel at di.fm was really helpfull 😉😃
