Query to find conflicting shows
Posted: Sat Mar 03, 2007 8:44 pm
I'm building an admin system for people to add shows from a drop-down menu to a schedule. It allows them to insert a show in at the "next available time slot". that works just fine. Where I am having trouble is when they want to enter a specific time for the show to be put into the schedule. The thing I'm having trouble with is checking to see that there are no other shows within the time slot they are requesting. There are two three tables involved here...
schedule:
id,
start_time
end_time
show_id
allshows
id
channel_id
duration
name
url
channels
id
name
So for each channel, there is an schedule administration panel. Now let's say there are three shows in the schedule for today:
Show number one 12:00am - 12:15am
Show number two 12:15:01am - 12:33:05am
Show number three 12:35am - 1:10am
Now, let's say the user tries to enter a show at 12:10. They shouldn't be able to. Right now my query doesn't seem to be working right... it does sometimes, but others it does not. I also need to give them the ability to bump the shows if there are conflicts, but this can come later. This is my query now that is very flawed and buggy.
$start_time and $end_time are the start and end times of the show I want to insert. This project is driving my out of my mind. It was supposed to only take a little while and it's ended up taking FOREVER!
This is one of those projects where I am coming in after another developer and trying to add functionality to his code... it's a nightmare.
schedule:
id,
start_time
end_time
show_id
allshows
id
channel_id
duration
name
url
channels
id
name
So for each channel, there is an schedule administration panel. Now let's say there are three shows in the schedule for today:
Show number one 12:00am - 12:15am
Show number two 12:15:01am - 12:33:05am
Show number three 12:35am - 1:10am
Now, let's say the user tries to enter a show at 12:10. They shouldn't be able to. Right now my query doesn't seem to be working right... it does sometimes, but others it does not. I also need to give them the ability to bump the shows if there are conflicts, but this can come later. This is my query now that is very flawed and buggy.
Code: Select all
SELECT
*
FROM
schedule AS sched
INNER JOIN allshows AS shows ON
sched.show_id = shows.show_id
WHERE
sched.channel_id = " . $this->channel_id . "
AND
end_date_time
BETWEEN
" . mysql_real_escape_string($start_time) . "
AND
" . mysql_real_escape_string($end_time) . "
OR
sched.channel_id = " . $this->channel_id . "
AND
start_date_time
BETWEEN
" . mysql_real_escape_string($start_time) . "
AND
" . mysql_real_escape_string($end_time) . "
ORDER BY
start_date_timeThis is one of those projects where I am coming in after another developer and trying to add functionality to his code... it's a nightmare.

- w00t, I'm one happy ninja! This stupid project is finally done!