How to store multiple results in one table only MySql and PHP


Problem :

I am trying to generate Hourly Report using MySQL and PHP.

I want to show how many cases came in between 08:00:00 - 09:00:00 like this of 24 hours and Divide it Date Wise. Right now I am getting the result but I have created multiple result queries like this:

$result = mysqli_query($con,"SELECT COUNT(IST_In_Time), DATE(IST_In_Time) FROM spillreport WHERE TIME (IST_In_Time) BETWEEN '19:00:00' AND '20:00:00' GROUP BY DATE(IST_In_Time)");
$result1 = mysqli_query($con,"SELECT COUNT(IST_In_Time), DATE(IST_In_Time) FROM spillreport WHERE TIME (IST_In_Time) BETWEEN '08:00:00' AND '09:00:00' GROUP BY DATE(IST_In_Time)"); 

And storing it different while loops:

while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['DATE(IST_In_Time)'] . "</td>";
echo "<td>" . $row['COUNT(IST_In_Time)'] . "</td>";
echo "<td></td>";
}

while($row = mysqli_fetch_array($result1)) {
echo "<tr>";
echo "<td>" . $row['DATE(IST_In_Time)'] . "</td>";
echo "<td></td>";
echo "<td>" . $row['COUNT(IST_In_Time)'] . "</td>";
}

I know this bad practice, but I don't have time to take a better approach. I want to display in a single table where rows will automatically increase according to dates and columns will be predefined like 7:00am-8:00am , 8:00am-9:00am and so on..

EDIT: Using Below Answers I got the following Result:

Query: SELECT DATE(IST_In_Time) date,HOUR(IST_In_Time) hour,COUNT(*) count FROM spillreport WHERE DATE(IST_In_Time)>= '2014-07-22 00:00:00' AND DATE(IST_In_Time)<= '2014-07-26 00:00:00' GROUP BY HOUR(IST_In_Time), DATE(IST_In_Time) ORDER BY DATE(IST_In_Time)ASC

Result:

date    hour    count
2014-07-22  19  1
2014-07-22  14  1
2014-07-23  18  28
2014-07-23  15  1
2014-07-23  19  26
2014-07-23  17  1
2014-07-23  20  8
2014-07-24  11  34
2014-07-24  19  2
2014-07-24  8   1
2014-07-24  12  35
2014-07-24  13  23
2014-07-24  15  37
2014-07-24  14  52
2014-07-24  10  34
2014-07-24  16  59
2014-07-24  9   15
2014-07-24  17  46
2014-07-24  18  25
2014-07-25  8   1
2014-07-26  19  1
2014-07-26  8   2

But I want to group only unique dates and hours should be divided horizontally like this:

    Hours 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23    
2014-07-22 - - - - - - - - - - -  -   -  -  1  -  -  -  -  1  -  -  -  -
2014-07-23 - - - - - - - - - - -  -   -  -  -  1  - 1 28  26  8  -  -  -
2014-07-24
2014-07-25
2014-07-26

Like this!

EDIT 2:

select client_id,
  max(case when rownum = 1 then date end) Date1,
  max(case when rownum = 2 then date end) Date2,
  max(case when rownum = 3 then date end) Date3
from
(
  select client_id,
    date,
    @row:=if(@prev=client_id, @row,0) + 1 as rownum,
    @prev:=client_id 
  from yourtable, (SELECT @row:=0, @prev:=null) r
  order by client_id, date
) s
group by client_id
order by client_id, date

I got this code to create pivot table, can anybody guide me how do I create this for mine?



Solution :

I think you want counts by the hour, so as commented you can use HOUR. e.g. to count number of events per hour today from a table I have to hand:

 mysql> SELECT DATE(event_time) date,HOUR(event_time) hour,COUNT(*) count 
   FROM events WHERE event_time > curdate() GROUP BY DATE(event_time),HOUR(event_time);
 +------------------+------------------+----------+
 | date             | hour             | count    |
 +------------------+------------------+----------+
 | 2014-07-26       |                0 |       30 |
 | 2014-07-26       |                1 |       21 |
 | 2014-07-26       |                2 |       11 |
 | 2014-07-26       |                3 |       29 |
 | 2014-07-26       |                4 |       25 |
 | 2014-07-26       |                5 |       22 |
 | 2014-07-26       |                6 |       46 |
 | 2014-07-26       |                7 |       42 |
 | 2014-07-26       |                8 |       26 |
 | 2014-07-26       |                9 |       58 |
 | 2014-07-26       |               10 |       11 |
 +------------------+------------------+----------+
 11 rows in set (0.00 sec)

Edit: Updated to add missing grouping by date.

I'd suggest grouping by date,hour and ordering by date,hour too to get a nicely ordered result set. That should produce the data you need for any post processing in PHP. After that it's a PHP formatting job.


Mysql Tutorials

Mysql Howto..

How to do a X or Y in where query mysql for a single variable?

Php or MYSQL solution on how to display MYSQL data by alternating rows depending on a criteria

How do you add comments to mysql queries, so they show in logs?

MySQL - How to set auto_increment_increment permanently, to last server restart?

how to implement “contains” function in Mysql4.x?

How to set value of 'a' depending on value of 'b' in mysql

Installed mysql from source on linux and then how to get it work with php?

How to find last mysql dump file in directory and inport it

How to remove a prefix name from every table name in a mysql database

how to update a VARCHAR column value with RegEx?

How to parse a string to insert a row into a mysql table?

How to insert current date into mysql db using java

How to Save an Append or Delete Query in MySQL

How to log all changes in a mysql table to a second one?

How to delete all relative records in MySQL InnoDB?

how to list mysql query result in particular sequence?

How can I escape the input to a MySQL db in Python3?(Solved)

how to select top 5 products from table

How to update a field value by default in mysql in single query

How to order by a count(*) field?

How to convert second select statement result to CSV String in main select statement?

How to get database name from postgres/mysql cursor object

How to add appending data to MySQL through R?

how to see new inserted data in textbox ? (java netbeans mysql)

How to populate db with sample data using PHP?

How to preserve original ID in JOIN MySQL query/ operation?

How to do complicate ORDER BY in MySQL

I have latin1 encoded data sitting in a UTF-8 mysql database, how do I fix this?

How to retrieve rows from table A whose VARCHAR column has a partial match on another VARCHAR col. in table B?

How To Use MySQL Indexes Properly?