php - 当 mysql 表中的开始时间在 PHP 中大于 1 小时时获取 Null

Question

我有一个根据时间计算成本的表格。我需要根据时间显示表格，限制为 1 小时。如果时间大于 1 小时，它应该在成本列中显示 null。我该怎么做。

dashboard.php

        <?php
                    $host    = "localhost";
                    $user    = "root";
                    $pass    = "";
                    $db_name = "test2";
                    $lastId="";


                     $date = strtotime('now') - 3600;  
                     print_r($date);  
                     $week = strtotime('now') - 604800;  
                     $month = strtotime('now') - 2592000 ;        

                    //create connection
                    $con = mysqli_connect($host, $user, $pass, $db_name);

                    //test if connection failed
                    $result = mysqli_query($con,"SELECT SUM(cost) FROM track_data WHERE start_time > 
                      DATE_SUB(NOW(), INTERVAL 1 HOUR) ");

                    $result1 =  mysqli_query($con,"SELECT SUM(cost) FROM track_data WHERE start_time > 
                      DATE_SUB(NOW(), INTERVAL 1 WEEK)");

                    $result2 =  mysqli_query($con,"SELECT SUM(cost) FROM track_data WHERE start_time > 
                      DATE_SUB(NOW(), INTERVAL 1 MONTH)");


                    ?>

    <div class="col-md-6 col-md-offset-3">
            <table class="table table-hover table-striped">    
            <tr class="t_head"> 
              <th>  Days </th>
              <th> Usage  </th>
              <th> Total Cost  </th>

            </tr>       
    <?php


          while($row = mysqli_fetch_array($result))


    {



      echo "<tr id='emp_salary'>"; 

     echo "<td> Today </td>";
      echo "<td>1 Hour</td>";
    echo "<td>" .$row[0]. "</td>";

    echo "</tr>";
    }
?>
</table>
</div>

如您所见，上图中总成本列为空。我应该得到 NUll 或零，而不是空值。

Answer 1

你应该使用COALESCE

因为如果您的 sql 查询未返回任何行或行的总和为空，那么您的回显实际上不会回显任何内容。

试试这个

$result = mysqli_query($con,"SELECT COALESCE(SUM(cost),0) FROM track_data 
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR) ");