in my php mysql report, I extract data from mysql table with below code. everything is working fine, but i unable to do sum in the end . Is there any way by which i can sum employee work time. (i tried rollover, its not fulfilling my requirements, can any expert guide me how to do with loops so that i can get following output as show in below pic
You’ve already got all the looping you need. While you’re doing the outputing of the pre-sum row, do the addition. It’s not the best way to store the data, but it can still be worked with.
When I was new to SQL, I always looped. Then I realized that let the database do the job was normally smarter. Without table structure and desired output it is hard to say. Do a db fiddle so it is easier to understand.
Get the all the data in the desired format from the database and then create and add to table.
View is a way to make a query look simpler. IMO there is no need for views unless you use the query in many, many places (DRY sort of). If you only use this query once it is already DRY…
What is the wrktime column definition and what are some of the actual values in it? Is the 8:00 example actually stored as 8:00 or is it 8:00:00?
Next, the picture you posted makes no sense. What is the actual result you are trying to produce? Are you trying to list all the rows for every employee (for some range of dates or for some job id?), with a total row between each employee? Or are you trying to list one row for each employee and a grand total at the end?
The SUM(wrktome) wasn’t the total number of seconds. It was treating the values as integers, e.g. 800 + 730 + 730 = 2260 and is meaningless because times are a mixed base number and cannot simply be added.
Regardless, of what result you are trying to produce, these values are quantities of hours:minutes (through I suspect you are actually storing them in the database as minutes:seconds), not time of day values. The math would involve splitting the values at the : character and calculating the number of minutes = hours x 60 + minutes, adding the number of minutes together, then converting the total number of minutes back to hours:minutes at the end for display purposes.
I tried to solve it via looping as shown in below code but now the issue is that it only sum hours, and ignoring minute. (but my worktime column is storing in time format hh:mm)
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM tmmach ORDER BY id DESC LIMIT 30";
if ($result = mysqli_query($link, $sql)) {
if (mysqli_num_rows($result) > 0) {
echo '<table class="table table-bordered table-striped">';
echo "<thead>";
echo "<tr>";
echo "<th>Emp-Id</th>";
echo "<th>Name</th>";
echo "<th>Company</th>";
echo "<th>Date</th>";
echo "<th>worktime</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
$totalWorkTime = 0; // Initialize the variable for sum
while ($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['empid'] . "</td>";
echo "<td>" . $row['empname'] . "</td>";
echo "<td>" . $row['company1'] . "</td>";
echo "<td>" . date('d-M-Y', strtotime($row['date1'])) . "</td>";
echo "<td>" . $row['wrktime'] . "</td>";
// Update the sum variable
$totalWorkTime += $row['wrktime'];
echo "</tr>";
}
echo "</tbody>";
// Display the sum row
echo "<tfoot>";
echo "<tr>";
echo "<td colspan='4'><strong>Total Work Time:</strong></td>";
echo "<td><strong>" . $totalWorkTime . "</strong></td>";
echo "</tr>";
echo "</tfoot>";
echo "</table>";
// Free result set
mysqli_free_result($result);
} else {
echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
// Close connection
mysqli_close($link);
?>
Maybe it will sound strange - I would like to ask, how do you calculate sum of working hours on a paper, if Your variables is in format hh:mm ? So apply same principle to your code
