php查询每个小时的数据,php – MySQL显示表中每小时的条目数

New link – results off by 1我需要在一个表格中回显给定日期每小时的条目数,即使它们是0.这似乎正在发生,但是以一种奇怪的方式…正如你所看到的,小时列不是在玩球这应该是0：00-1 …. 22.00-23.00等.预期的结果总数是19,当你将结果加在一起时也是正确的.到了我现在不知道脚本中发生了什么的地步,不知道从哪里开始修复格式问题.任何帮助非常赞赏

// 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 "

" . $hours[$i] . "" . $tapsa . "";

}

}

?>

最佳答案 我不是PHP的专家,但你的i变量并没有增加内部只需加上$hours [$i]或者只是从$timea [‘hours’]获得你的标签

你可能不需要初始化i = 0

while($timea = mysql_fetch_assoc($qtimea)) {

$tapsa = $timea['tapcount'];

$hour_label = $timea['hours'];

echo "

" . $hour_label . "" . $tapsa . "";

}