I've looked at a lot of the stackoverflow answers around this subject, but they all seem to be coming at it from a different direction. Here's what I want to do, and at this stage I'm more head-scratching as to the way to approach it than writing the specific code.
The task is to display shows that a user could see based on their existing commitments, whether that be not available or already seeing something else.
Table A contains a list of start & end times of user unavailability
Table B contains a list of start & end times of user existing bookings.
Table C contains a list of start & end times of all shows.
To add more detail to this, I'm using FullCalendar agendaView to display a users commitments. In the gaps I want to display shows they could see. The code to display the unavailability and existing commitments is already written, but I have no clue now trying to find shows in Table C that don't clash with entries in Tables A & B.
Any ideas?
My solution is for PHP, but I'm sure there is a better solution hiding in an SQL stored procedure or something.
If you work with Unix Time you can simply test for open slots by comparing begin and end times:
Iterate through this list and compare an open slot with each show in Table C. Something like
if (
(showtime['begin'] >> opentime['begin']) &&
(showtime['end'] << opentime['end'])
) return true;
If you find a match, print it out.
EDIT:
After thinking a bit I came up with possibly a better solution. There is no way to get away from not comparing everything item by item. So the trick is to instead of doing all the processing at once, spread it out over the entire lifecycle of the app:
This way all the grunt work is done in SQL and not in PHP, so it could be faster.