CODEWITHSUNDEEP
phpmysqlmysqli
aback
aback

Reputation: 3

MYSQL counts the number of visitors from the database table in the hour table starting with the format from 1:00 to 23:00

I have a visitor database table like this with the name of the "visitor" table with field "id" and "date".

+----+-------------------------------+
| id |date                           |
+----+-------------------------------+
| 1  |2018-01-01 00:12:09            |
| 2  |2018-01-01 01:00:00            |
| 3  |2018-01-01 02:00:00            |
| 4  |2018-01-01 02:15:00            |
| 5  |2018-01-01 03:20:01            |
| 6  |2018-01-01 04:00:00            |
+----+-------------------------------+

and want me to want to display in a table form like this clock grouping 00-01=>01 .... 23

visitors on this day 2018-01-01
+---------------------------------------+
| hour  | visitor                       |
+-------+-------------------------------+
| 01:00 |2 visitor                      |
+-------+-------------------------------+ 
| 02:00 |2 visitor                      |
+-------+-------------------------------+
| 03:00 |1 visitor                      |
+-------+-------------------------------+ 
| 04:00 |1 visitor                      |
+-------+-------------------------------+ 
| 05:00 |0 visitor                      |
+-------+-------------------------------+ 
| 06:00 |0 visitor                      |
+-------+-------------------------------+
| etc ...                               |
+-------+-------------------------------+ 
| 23:00 |0 visitor                      |
+-------+-------------------------------+

Can anyone solve this problem?

Upvotes: 0

Views: 110

Answers (1)

Sohrab Yousefi
Sohrab Yousefi

Reputation: 116

Hope it will help you :

$query = "SELECT DATE_FORMAT(`date`, '%H:00') AS `HOUR`, COUNT(`id`) AS `VISITOR` 
    FROM `visitor` 
    WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '2018-01-01' 
    GROUP BY DATE_FORMAT(`date`, '%H:00')";

$res = mysqli_query($conn, $query);
$list = [];
while($row = mysqli_fetch_assoc($res)){
    $list[] = ["HOUR" => $row["HOUR"], "VISITOR" => $row["VISITOR"]];
}

echo "<table><thead><tr><th>HOUR</th><th>VISITOR</th></tr></thead><tbody>";

for($i=1;$i<24;$i++){
    foreach($list as $key=>$arr){
        $h_arr = explode(":", $arr["HOUR"]);
        if(sprintf('%02d', $h_arr[0]) == sprintf('%02d', $i)){
            $visitor = $arr["VISITOR"];
            break;
        }else{
            $visitor = 0;
        }
    }
    echo "<tr><td>".sprintf('%02d', $i)."</td><td>".$visitor."</td></tr>";
}

echo "</tbody></table>";

Upvotes: 1

Related Questions