CODEWITHSUNDEEP
phphtmlmysql
Joe Oliver Bailey
Joe Oliver Bailey

Reputation: 43

MySQL to display number of entries per hour in a table

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>";
    }
}
?>

Upvotes: 4

Views: 262

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

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

Upvotes: 2

Related Questions