So I need to get the total sum of items from my time sheet here.
It has an item and lets say it’s for 2 hours work at $95.00 an hour, I can get that math to work, no issue there, however,
for some reason I can not wrap my head around adding up all the items to get the total
I need
Item | Hours | Line total
A Item | 2.5 | $200.00
B Item | 4.0 | $250.00
Total Price: $450.00 <----- how do I grab the total?
In my head im thinking this is easy, simply run it threw a foreach(), but for some reason im at a loss from there LOL
<tr>
<th>Item ID</th>
<th>Description</th>
<th>User</th>
<th>Hours</th>
<th>Amount</th>
</tr></thead>
<?php foreach ($data['info'] as $row):
$amount = $row['rate'] * $row['deductedHours']; //<-------------This is what needs to be added to get the total price for the line items
?>
<tbody>
<tr>
<td><a href="./Item/<?php echo $row['id']; ?>">
<?php echo $row['id']; ?></a></td>
<td><?php echo $row['description']; ?></td>
<td><?php echo $row['firstName'] . " " . $row['lastName']; ?></td>
<td><?php echo $row['deductedHours']; ?></td>
<td><?php echo "$" . number_format($amount, 2); ?></td> <?php //<-------- Amount for each line item has to go here (which is working)
</tr>
<?php endforeach; ?>
<tr>
<td class="sub_total" colspan="2"></td>
<td class="sub_total">Total Hours:</td>
<td class="sub_total"><?php //echo $totalAmount; //<----------- The total amount needs to go here ?>
</td>
<td class="sub_total">
</td>
</tr>
</tbody>
</table>
but only if the result should show just the sums, and not the details
if the details need to be shown, you would do the sums in php
(you could do the sums in mysql too, but then you’d need two queries that would be UNION’ed together, and then there’s a tricky matter of sorting/interleaving…)
I was thinking two separate data sets. If you get into large data sets you don’t want to be calculating a sum through PHP, I think a new mysql connection would be worth it at that point.
$sCriteria = "DATE RANGE HERE";
$rows = $db->query('SELECT ROUND((TIME_TO_SEC(sr.endtime) - TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 ) AS totalHours FROM tablename WHERE $sCriteria');
$totals = $db->query('SELECT sum(ROUND((TIME_TO_SEC(sr.endtime) - TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 )) AS totalHours FROM tablename WHERE $sCriteria');
for each ($rows as $row) {
//echo all rows
}
echo $totals['totalHours'];
Minus the obvious syntax errors and missing logic. All one needs to do is call the same query with the aggregate functions under a new variable.
Though your thought brings up a new question I’d like to find a solution for. If there were group by clauses, perhaps per month? And we wanted the subtotals below each month, what would eb the best way to handle the data at that point? I know that for a quick solution I’d probably write a loop to do exactly what I did above for each item in the group by, but I don’t know if I like that method. Youd have two SQL transactions for each item grouped in that case.
