CODEWITHSUNDEEP
javascriptphphtmlmysqlsql
user3766876
user3766876

Reputation: 15

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

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?

Upvotes: 0

Views: 153

Answers (2)

Nick
Nick

Reputation: 1412

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.

Upvotes: 1

Beatles1692
Beatles1692

Reputation: 5320

You can use Union to (as is obvious) union the result set of different queries :

SELECT * FROM t1
UNION
SELECT * FROM t2

You can read more here.

Upvotes: 0

Related Questions