Sum time (in hours minute) for each employee

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

 $sql = "SELECT * FROM timetablw 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>";
                                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>";
                                      
                                       
                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            // Free result set
                            mysqli_free_result($result);
                        } else{
                            echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
                        }

Capture

Why not let the database do the job? Pseudocode. Not tested.

SELECT empid, sum(worktime) FROM timetable GROUP BY 1

because he wants to fill in the rest of the table, also :stuck_out_tongue:

strtotime, intval, addition.

Then depending on how you want to handle values greater than 24 hours, either stuff it back into a date or do divisional maths.

its gives sum in seconds rather than hour minute format

So the worktime is in seconds?

no, its in time format, hh:mm but interestingly its gives sum in seconds

yes loops will be better

I am not familiar with mysql, but there must be a way to format or cast the worktime.

1 Like

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.

1 Like

No loops are not better. It’s always the most worse idea to load all data from the database. What if your database grows to 1 million records?

ok sir

so what is better way to do that ? can you guide

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.

1 Like

can we do it with views as well???
by creating view from mysql table in phpmyadmin

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…

1 Like

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.

1 Like

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

Time is a time not a duration. If you store working hours, this is a duration of whatever, hours, minutes, seconds etc. but it is no time…

So change your working time column to int and use a simple SUM() query in SQL.