Query to find conflicting shows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Query to find conflicting shows

Post 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
Last edited by Luke on Mon Mar 05, 2007 10:29 pm, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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??
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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        |-----|
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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
)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

...
or (
 $start_date <= start_date_time
 and $end_date >= end_date_time
)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Awesome... not sure why I didn't think of that though. :? Seems to be working now. thanks
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Image - w00t, I'm one happy ninja! This stupid project is finally done! :D
Post Reply