I 只是做了第一个 sql 语句。请参阅演示链接,了解我到目前为止所做的事情。演示
我想在其他列中显示其他三个 sql 语句的结果。检查我想做什么,最后想做所有时隙值的总和。演示
到目前为止,我已经尝试了以下代码:
<?php
$first_time_slot = "00:00:00 - 06:00:00";
$second_time_slot = "06:01:00 - 12:00:00";
$third_time_slot = "12:01:00 - 18:00:00";
$fourth_time_slot = "18:01:00 - 23:59:59";
$sql1 = mysql_query("SELECT date(`calldate`) as date, count(`src`)as total_caller, sum(`duration`) as total_duration FROM `cdr` WHERE time(`calldate`) BETWEEN '00:00:00' AND '06:00:00' AND `accountcode` = '09614008155' group by date order by date DESC");
$sql2 = mysql_query("SELECT date(`calldate`) as date, count(`src`)as total_caller, sum(`duration`) as total_duration FROM `cdr` WHERE time(`calldate`) BETWEEN '06:01:00' AND '12:00:00' AND `accountcode` = '09614008155' group by date order by date DESC");
$sql3 = mysql_query("SELECT date(`calldate`) as date, count(`src`)as total_caller, sum(`duration`) as total_duration FROM `cdr` WHERE time(`calldate`) BETWEEN '12:01:00' AND '18:00:00' AND `accountcode` = '09614008155' group by date order by date DESC");
$sql4 = mysql_query("SELECT date(`calldate`) as date, count(`src`)as total_caller, sum(`duration`) as total_duration FROM `cdr` WHERE time(`calldate`) BETWEEN '18:01:00' AND '23:59:59' AND `accountcode` = '09614008155' group by date order by date DESC");
?>
<table border="1" width="90%" class="fancy">
<caption style="color:#FF0000; margin-bottom:10px; font-size:16px;">Showing Caller History.</caption>
<tr>
<th>Date</th>
<th>12am - 6am</th>
<th>6am - 12pm</th>
<th>12pm - 6pm</th>
<th>6pm - 12am</th>
<th>Total Caller</th>
</tr>
<?php if (isset($first_time_slot)){
while ($row1 = mysql_fetch_assoc($sql1)) { ?>
<tr>
<td><?php echo $row1['date']; ?></td>
<td><?php echo $row1['total_caller']; ?></td>
<?php }} ?>
</tr>
</table>
到目前为止,我只应用第一个sql语句。如何将其他三个sql语句应用于其他三列。请帮助我。提前谢谢。
我无法测试,但为了使事情变得更容易,您可以使用 4 个查询保留您的代码并像这样更改您的 php 代码(这不是:)的最佳解决方案):
<tr>
<?php if (isset($first_time_slot)){
if ($row1 = mysql_fetch_assoc($sql1)) { ?>
<td><?php echo $row1['date']; ?></td>
<td><?php echo $row1['total_duration']; ?></td>
<?php }
if ($row1 = mysql_fetch_assoc($sql2)) { ?>
<td><?php echo $row1['total_duration']; ?></td>
<?php }
if ($row1 = mysql_fetch_assoc($sql3)) { ?>
<td><?php echo $row1['total_duration']; ?></td>
<?php }
if ($row1 = mysql_fetch_assoc($sql4)) { ?>
<td><?php echo $row1['total_duration']; ?></td>
<td><?php echo $row1['total_caller']; ?></td>
<?php }
} ?>
</tr>
正如您所建议的,您可以使用这样的一个查询
SELECT date(`calldate`) as date, count(`src`)as total_caller, sum(`duration`) as total_duration
FROM `cdr`
WHERE (time(`calldate`) BETWEEN '00:00:00' AND '06:00:00')
Or (time(`calldate`) BETWEEN '06:01:00' AND '12:00:00')
Or (time(`calldate`) BETWEEN '12:01:00' AND '18:00:00')
Or (time(`calldate`) BETWEEN '18:01:00' AND '23:59:59')
AND `accountcode` = '09614008155' group by date order by date DESC