MySQL在表中显示每小时的条目数量 [英] MySQL to display number of entries per hour in a table

问题描述

新的链接 - 结果为1 我需要在表格中回显数字，即使它们是0，也是如此。这似乎正在发生，但以一种奇怪的方式......正如你所看到的，时间栏不是在打球，因为这应该是0：00-1。 ... 22.00-23.00等。预计结果总数为19，当您将结果汇总在一起时也是正确的。到了现在，我不知道脚本中发生了什么，也不知道从哪里开始修复格式问题。非常感谢任何帮助。 HTML页面当前结果

New link - results off by 1I need to echo in a table the number of entries per hour for a given date, EVEN IF they are 0. This seems to be happening but in a weird way... As you can see, the hours column is not playing ball as this should be 0:00-1 .... 22.00-23.00 etc. The total number of result expected is 19 which is also correct when you sum the results together. Got to the point where I now don't know what is happening where in the script and no idea where to start fixing the formatting issues. Any help much appreciated.Current Result in HTML Page

// Get list of times per hour for reader 
$sqltimea = "SELECT   CONCAT(Hour, ':00-', Hour+1, ':00') AS hours,
 COUNT(r.readerid) AS tapcount
FROM 
   (
     SELECT  '0' AS Hour
     UNION ALL SELECT  '1' UNION ALL SELECT  '2' UNION ALL SELECT  '3'
     UNION ALL SELECT  '4' UNION ALL SELECT  '5' UNION ALL SELECT  '6'
     UNION ALL SELECT  '7' UNION ALL SELECT  '8' UNION ALL SELECT  '9'
     UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '12'
     UNION ALL SELECT '13' UNION ALL SELECT '14' UNION ALL SELECT '15'
     UNION ALL SELECT '16' UNION ALL SELECT '17' UNION ALL SELECT '18'
     UNION ALL SELECT '19' UNION ALL SELECT '20' UNION ALL SELECT '21'
     UNION ALL SELECT '22' UNION ALL SELECT '23'
 ) AS h
  LEFT JOIN taps t ON HOUR(t.`time`) = Hour 
AND
    DATE(t.time) = '2016-01-15' LEFT JOIN
 readers r
 ON r.readerid = t.readerid AND r.type = 'A'
GROUP BY Hour
ORDER BY Hour";
$qtimea = mysql_query($sqltimea);

// THE CHALLENGE! Put it in a table!

$hours = array("0:00-1:00", "1:00-2:00", "2:00-3:00", "3:00-4:00", "4:00-5:00", "5:00-6:00", "6:00-7:00", "7:00-8:00", "8:00-9:00", "9:00-10:00", "11:00-12:00", "12:00-13:00", "13:00-14:00", "14:00-15:00", "15:00-16:00", "16:00-17:00", "17:00-18:00", "18:00-19:00", "19:00-20:00", "20:00-21:00", "21:00-22:00", "22:00-23:00", "23:00-24:00");

for($i = 0; $i <= 23; $i++) {
    while($timea = mysql_fetch_assoc($qtimea)) {
        $tapsa = $timea['tapcount'];
        echo "<tr><td>" . $hours[$i] . "</td><td>" . $tapsa . "</td></tr>";
    }
}
?>

推荐答案

$ c> i 变量不会在内增加，而只需添加 $小时[$ i ++] 或者只是从 $ timea ['hours']

Im not expert on PHP, but your i variable doesnt get incremented inside the while just add $hours[$i++] or just get your label from $timea['hours']

获取标签并且您可能不需要 for 只是初始化 i = 0

And you probably dont need the for just initialize i = 0

while($timea = mysql_fetch_assoc($qtimea)) {
        $tapsa = $timea['tapcount'];
        $hour_label = $timea['hours'];
        echo "<tr><td>" . $hour_label . "</td><td>" . $tapsa . "</td></tr>";
}

这篇关于MySQL在表中显示每小时的条目数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！