I tried to calculate total time in my script, but the issue is its only summing hours and ignoring minutes. for example if i have 2 working hours times in mysql table first 7:34 and 2nd 8:46 then instead of givng total time as 16:20, its display sum as 15. (which means its only sum hours)
here is my script
<?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);
?>
My guess is that it’s coming in from the database as strings “7:34” and “8:46”, and when you attempt to treat it like a number, PHP truncates it at the first non-numeric character, in this case the colon in each. If you stored the value as minutes, 454 and 526, and then handled the conversion whenever you are displaying it, that would make it easier.
Do you have warnings enabled? When I try a simple bit of test code like this:
<?php
$a = "7:34";
$b = "8:46";
$c = $a+$b;
I get:
Warning: A non-numeric value encountered in /in/C6lgC on line 4
Droop is correct. When you try and use something that isnt a number like a number, PHP tries to interpret it as a number.
“3” will interpret as 3.
“82imapotato99” will interpret as 82.
“7:34” will interpret as 7.
If you want SQL to return a number, time_to_sec would work.
Your previous examples show that you want to use the row data, so doing a SUM in SQL doesnt seem like the right answer for you.
If wrktime is a number representing the number of seconds, you can do direct maths with it in the variable as you have now. To output it to the table, send it into date, specify the format you want out, and the date function will give you the correct value up to the maximum of that format.
Why do i emphasize that last bit? Lets say you want a hours:minutes output. If you add up a bunch of times, and get 26 hours and 20 minutes, date will return 02:20. Why? because there aren’t 26 hours in a day. It’s 02:20 on the next day, according to the date function. (For the technical pedants, it will be 02:20:00 UTC on January 2nd, 1970)
offcourse i knew how i formatted date, but the issue is, i want sum of all those rows where date (the row you mentioned). and if i will use sum functions there , than it will only display just one row
So that line takes a number of seconds, and formats it in a date string.
In your new line, you want to take a number of seconds…and format it in a date string…
Do we see where i’m trying to lead the horse to the water yet?
