My movie database allows users to enter database into a sessions table which looks like this:
id | time | movie_id
Where time is the datetime the movie will be played and movie_id is a key relating to the movies table.
Now, I am trying to find consecutive days where the movies being played are the same. For example I want to be able to display.
3/6/2013 - 7/6/2013
Iron Man 3 7pm
Second Movie 10pm
ETC...
8/6/2013
Iron Man 3 8pm
9/6/2013 - 11/6/2013
Iron Man 3 7pm
Second Movie 10pm
Is there anyway to group using both the time and finding the consecutive days? I could change the table to a seperate date and time value if needed. Any help would be much appreciated. If this is only achievable in PHP, any idea on how to get started would also be appreciated.
Thank you. If you have any questions or this question isnt clear enough please ask.
Well, not so elegant, but working solution using GROUP_CONCAT aggregate function and variables within SELECT to groups similar consecutive days.
Your schema and example data set:
CREATE TABLE movies (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title CHAR(64) NOT NULL
);
CREATE TABLE schedule (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
time TIMESTAMP NOT NULL,
movie_id INT NOT NULL
);
INSERT INTO movies (title) VALUES
('Iron Man 3'),
('Second Movie')
;
INSERT INTO schedule (time, movie_id) VALUES
('2013-06-03 19:00:00', 1),
('2013-06-03 22:00:00', 2),
('2013-06-04 19:00:00', 1),
('2013-06-04 22:00:00', 2),
('2013-06-05 19:00:00', 1),
('2013-06-05 22:00:00', 2),
('2013-06-06 19:00:00', 1),
('2013-06-06 22:00:00', 2),
('2013-06-07 19:00:00', 1),
('2013-06-07 22:00:00', 2),
('2013-06-08 20:00:00', 1),
('2013-06-09 19:00:00', 1),
('2013-06-09 22:00:00', 2),
('2013-06-10 19:00:00', 1),
('2013-06-10 22:00:00', 2),
('2013-06-11 19:00:00', 1),
('2013-06-11 22:00:00', 2),
('2013-06-13 19:00:00', 1),
('2013-06-13 22:00:00', 2)
;
The query:
SELECT
DATE_FORMAT(min_date, '%e/%c/%Y') AS beg_date,
DATE_FORMAT(max_date, '%e/%c/%Y') AS end_date,
title,
LOWER(TIME_FORMAT(time, '%l%p')) AS `movie_time`
FROM
(SELECT
MIN(min_date) AS min_date,
MAX(max_date) AS max_date,
range_schedule
FROM
(SELECT
@min_date :=
IF(@range_schedule <=> day_schedule
AND days.date <=> ADDDATE(@max_date, 1),
@min_date,
days.date
) AS min_date,
@max_date := days.date AS max_date,
@range_schedule := days.day_schedule AS range_schedule
FROM
(
SELECT DATE(time) AS `date`, GROUP_CONCAT(CONCAT(TIME(time), '-', movie_id) ORDER BY time) AS day_schedule
FROM schedule
GROUP BY DATE(time)
ORDER BY DATE(time)
) AS days,
(SELECT
@min_date := '0000-00-00',
@max_date := '0000-00-00',
@range_schedule := NULL
) r
) days_of_ranges
GROUP BY min_date, range_schedule
) ranges
JOIN schedule ON DATE(schedule.time) = ranges.min_date
JOIN movies ON movies.id = movie_id
ORDER BY min_date, time
;
And the result:
| BEG_DATE | END_DATE | TITLE | MOVIE_TIME |
-----------------------------------------------------
| 3/6/2013 | 7/6/2013 | Iron Man 3 | 7pm |
| 3/6/2013 | 7/6/2013 | Second Movie | 10pm |
| 8/6/2013 | 8/6/2013 | Iron Man 3 | 8pm |
| 9/6/2013 | 11/6/2013 | Iron Man 3 | 7pm |
| 9/6/2013 | 11/6/2013 | Second Movie | 10pm |
| 13/6/2013 | 13/6/2013 | Iron Man 3 | 7pm |
| 13/6/2013 | 13/6/2013 | Second Movie | 10pm |
All that you need to do in PHP is to store last BEG_DATE and END_DATE values to compare them with current ones to decide when to output range header.