Page 1 of 1

Query to find conflicting shows

Posted: Sat Mar 03, 2007 8:44 pm
by Luke
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.

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_time
$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. Image

Posted: Sat Mar 03, 2007 9:19 pm
by califdon
I had to do something somewhat similar recently, and it IS a confusing task, for sure. I assume you want to use the query to determine if any conflicts exist, so if this query returns any rows, you can't insert the new show, right?

First, I think you may need to use parentheses in your WHERE clause. Whenever you have multiple ANDs and ORs in a statement, it's hard to see how the parser will combine them, unless you use parentheses. So I'd try something like:

Code: Select all

...
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) . " ) )
...
BTW, I heard someone describing a nearly identical project at a recent MySQL Meetup - was that you??

Posted: Sat Mar 03, 2007 10:20 pm
by Luke
califdon wrote:I had to do something somewhat similar recently, and it IS a confusing task, for sure. I assume you want to use the query to determine if any conflicts exist, so if this query returns any rows, you can't insert the new show, right?

First, I think you may need to use parentheses in your WHERE clause. Whenever you have multiple ANDs and ORs in a statement, it's hard to see how the parser will combine them, unless you use parentheses. So I'd try something like:

Code: Select all

...
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) . " ) )
...
Thanks don, I'll give it a go :)
califdon wrote:BTW, I heard someone describing a nearly identical project at a recent MySQL Meetup - was that you??
wasn't me... could have been the client though. :wink:

Posted: Sun Mar 04, 2007 4:49 am
by Weirdan
the entered show conflicts with the existing one in any of the following cases:
1. if there are any shows starting while the entered show is still running:

Code: Select all

db        |---------|
user |------|
2. if there are any shows ending while the entered show is already running:

Code: Select all

db   |--------------|
user         |---------|
3. if there are any shows that falls entirely into the time-frame of the entered show:

Code: Select all

db        |---|
user  |-----------|
Just assemble your WHERE clause to the above conditions and you will be fine. Though consider the corner cases like the following:

Code: Select all

db    |-----|
user        |-----|

Posted: Sun Mar 04, 2007 10:49 pm
by Luke
This is what I've come up with. It seems to cover all those cases but I'm not sure.

Code: Select all

SELECT
    *
FROM
    `uvn_schedule`
WHERE
(
    channel_id = $channel
AND
    $end_time
BETWEEN
    start_date_time
AND 
    end_date_time
)
OR
(
    channel_id = $channel
AND
    $start_time
BETWEEN
    start_date_time
AND
    end_date_time
)

Posted: Mon Mar 05, 2007 12:06 am
by Weirdan

Code: Select all

...
or (
 $start_date <= start_date_time
 and $end_date >= end_date_time
)

Posted: Mon Mar 05, 2007 12:20 am
by Luke
Awesome... not sure why I didn't think of that though. :? Seems to be working now. thanks

Posted: Mon Mar 05, 2007 10:28 pm
by Luke
Image - w00t, I'm one happy ninja! This stupid project is finally done! :D