Hello,
I am querying a Mysql Database and displaying the results in a table. I need the results to Group twice and show totals.
What I need is the results to group by the employee name, then under that name group by the date field. Then at the end of each date total up four of the columns that contain numbers.
So the output would look like this:
Employee Name
Date
Description Job Activity Comments Hours OT Travel OT
XXXX XXXXX XXXXXX XXXXXX 3 2 1 1
XXXX XXXXX XXXXXX XXXXXX 2 0 0 1
XXXX XXXXX XXXXXX XXXXXX 1 0 1 1
Totals 6 2 2 3
Date
Description Job Activity Comments Hours OT Travel OT
XXXX XXXXX XXXXXX XXXXXX 3 2 1 1
XXXX XXXXX XXXXXX XXXXXX 2 0 0 1
XXXX XXXXX XXXXXX XXXXXX 1 0 1 1
Totals 6 2 2 3
Date
Description Job Activity Comments Hours OT Travel OT
XXXX XXXXX XXXXXX XXXXXX 3 2 1 1
XXXX XXXXX XXXXXX XXXXXX 2 0 0 1
XXXX XXXXX XXXXXX XXXXXX 1 0 1 1
Totals 6 2 2 3
Employee Name
Date
Description Job Activity Comments Hours OT Travel OT
XXXX XXXXX XXXXXX XXXXXX 3 2 1 1
XXXX XXXXX XXXXXX XXXXXX 2 0 0 1
XXXX XXXXX XXXXXX XXXXXX 1 0 1 1
Totals 6 2 2 3
Date
Description Job Activity Comments Hours OT Travel OT
XXXX XXXXX XXXXXX XXXXXX 3 2 1 1
XXXX XXXXX XXXXXX XXXXXX 2 0 0 1
XXXX XXXXX XXXXXX XXXXXX 1 0 1 1
Totals 6 2 2 3
etc....
My code so far, I can not figure how to do the second grouping by date and put the totals in.
mysql_select_db("time", $con);
$result = mysql_query("SELECT * FROM data WHERE labor_date BETWEEN '$start' AND '$end' Group By user_name, labor_date Order by last_name ASC");
if (mysql_num_rows($result)==0) {
echo "[warning_box]There are not any hours recorded for the time period that you chose. Please try again with a different set of dates.[/warning_box]
";
}
else {
$current_user_name = false;
while($row = mysql_fetch_array($result)) {
// listing a new employee? Output the heading, start the table
if ($row['user_name'] != $current_user_name) {
if ($current_user_name !== false)
echo '</table>'; echo '[divider_padding]';// if we're changing employee, close the table
echo '
<h5>'.$row['last_name'].', '.$row['first_name'].'</h5>[minimal_table]
<table>
<tr>
<th style="width:200px">Description</th>
<th style="width:75px" class="tableleft">Job</th>
<th style="width:75px" class="tableleft">Activity</th>
<th style="width:290px" class="tableleft">Comments</th>
<th style="width:64px" class="tableright">Hours</th>
<th style="width:64px" class="tableright">OT</th>
<th style="width:64px" class="tableright">Travel</th>
<th style="width:63px" class="tableright">TOT</th>
</tr>'
;
$current_user_name = $row['user_name'];
}
// output the row of data
echo '<tr>
<td>'.$row['description'].'</td>
<td class="tableleft">'.strtoupper($row['job']).'</td>
<td class="tableleft">'.$row['activity'].'</td>
<td class="tableleft">'.$row['comments'].'</td>
<td class="tableright">'.$row['rthours'].'</td>
<td class="tableright">'.$row['othours'].'</td>
<td class="tableright">'.$row['trthours'].'</td>
<td class="tableright">'.$row['tothours'].'</td>
</tr>
';
}
echo '</table>[/minimal_table]'; // close the final table
I am really stuck,
Any help would be greatly appreciated!!!!
