php - 如何计算具有 'time' 结构的列的总和并将其显示在 php 表中？

Question

我在 PMA 中运行了 SQL 语句 ($sql2)，它将 johnnides 的总计结果显示为“00:00:15.000000”。我不知道为什么这没有显示在 total.php 中。有什么想法/建议吗？

PMA

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

00:00:15.000000

total.php

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

Answer 1

在循环之前需要查询并获取$sql2的结果:

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

然后你可以使用$total作为总数。