CODEWITHSUNDEEP
phpmysql
user3666355
user3666355

Reputation: 83

How to calculate sum total of a column with 'time' structure and display it in php table?

I ran the SQL statement ($sql2) in PMA and it displays the result of the total for johnnides as "00:00:15.000000". I don't know why this isn't displaying in total.php. Any ideas/suggestions?

PMA

SELECT TIME(SUM(total)) AS total FROM timeSheet WHERE userName='johnnides'

00:00:15.000000

total.php

enter image description here

$sql="SELECT * FROM timeSheet, timeSheetUsers WHERE timeSheet.userName=timeSheetUsers.userName AND timeSheet.userName= '".$q."' ORDER BY startTime ASC";
$sql2="SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`total`))) FROM timeSheet WHERE userName = 'johnnides'";

echo "<table border ='1'  align='left'>
    <tr>
        <th bgcolor='#F0E68C'>username</th>
        <th bgcolor='#E0FFFF'>date</th>           
        <th bgcolor='#F0E68C'>starTime</th>
        <th bgcolor='#E0FFFF'>endTime</th>
        <th bgcolor='#F0E68C'>total</th>   
    </tr>";

    $result = mysqli_query($con,$sql);                                                              
    while($row = mysqli_fetch_array($result))
    {
        echo "<tr>";
            echo "<td bgcolor='#F0E68C'>" . $row['userName'] . "</td>";
            echo "<td bgcolor='#E0FFFF'>" . $row['date'] . "</td>";
            echo "<td bgcolor='#F0E68C'>" . $row['startTime'] . "</td>";
            echo "<td bgcolor='#E0FFFF'>" . $row['endTime'] . "</td>";
            echo "<td bgcolor='#F0E68C'>" . $row['total'] . "</td>";
        echo "</tr>";
    }
        echo "<tr>
                <th></th>
                <th></th>
                 <th></th>
                <th bgcolor='#00FF00'>Total Hours</th>
            <td bgcolor='#E0FFFF'>" . $row['total'] . "</td>";
        echo "</tr>";

echo "</table>";

Upvotes: 1

Views: 1800

Answers (1)

vaso123
vaso123

Reputation: 12391

You need to query and fetch the result of $sql2 before the loop:

$sql2="SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`total`))) AS total FROM timeSheet WHERE userName = 'johnnides'";
$res2 = mysqli_query($con, $sql2);
$totalRow = mysqli_fetch_assoc($res2);
$total = $totalRow['total'];

Then you can use $total as total.

Upvotes: 1

Related Questions