Mysql Question posted in
The official MySQL documentation can be found here.

sum of time giving one hour extra in php mysql

I am trying to calculate sum of time. Everything is working fine but sum of time displaying one hour extra. For example if sum of time need to be 20:23, its displaying 21:23. I dont knew why its given one hour extra in sum. (i came to knew via google, it can be solved by DateTimeImmutable but i dont knew how to use, as i never did it before) here is my code :

 $sql = "SELECT *, TIME_TO_SEC(wrktime) as bbb1 FROM tmmach ORDER BY ddd 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 "<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>";
        echo "<td>" . $row['bbb1'] . "</td>";

            // Update the sum variable
            $totalWorkTime += $row['bbb1'];
            $hhh = date('H:i',$totalWorkTime);   
  
  
            


            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>" . $hhh . "</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>';
    }


                    }
Tags:

2

Answers


  1. 0

    To calculate the sum of the "worktime" column, you can initialize a variable before the loop to accumulate the values and update it within the loop. Here’s a modified version of your code to include the sum calculation:

    $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>";

        $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>';
    }
}

    This modification includes a variable $totalWorkTime that is initialized before the loop and updated within the loop to accumulate the values. The total sum is then displayed in a new row in the table footer. Adjust the formatting as needed.

    Login or Signup to reply.
  2. 0

    You may obtain needed total sum in the query:

    SELECT empid, 
       IF(GROUPING(empid, empname, company), 'TOTAL', empname) empname, 
       company, 
       SUM(wrktime) wrktime
FROM timetablw
GROUP BY empid, empname, company WITH ROLLUP
HAVING GROUPING(empid, empname, company) IN (0, 7)
ORDER BY GROUPING(empid, empname, company), empid, empname, company

    Adjust SUM(wrktime) with according expression (depends on wrktime datatype and format).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search