Not allow multiple time range in DB.

[daveyc]

Hello all,

I'm currently working on a schedule script for my radio station. What it does is this.

It allows a user to enter a show into the DB which will be picked out and displayed on the "Who's on air" page. This bit isn't the problem.

I'm having a problem with actually making sure that the shows that are entered don't overlap.

At the moment, i've got a form where the user can choose the start time, in hours and minutes, and also the end time (In seperate dropdown menus. i.e One dropdown for the hour, and one for the minute).

I figured it would be easier to work with minutes rather than hours, so i've set it up like this:

Hour 00 = 00 Min
Hour 01 = 60 Min
Hour 02 = 120 Min.

and so on.

The minute dropdown times are in blocks of 10, from 00 to 50.

So, i get the start time 1:30am (which would equate to 90)
Then the end time 2:30am (which would equate to 150)

Then, i get the duration in minutes by taking the start time away from the end time.

So, now you know how i'm working with this, i need help and/or direction on the next part. (if you don't mind of course! lol)

If there is already a show in the database from say 1:30am - 2:30am on a Monday and someone tries to enter a show at 1:40am - 2:40am it would disallow them to do this, as it clashes with another show.

But ALSO, when a show runs after the current one, i don't want it to disallow in this case. e.g. Show A from 1:30am - 2:30am. Show B at 2:30am - 3:30am (this would be allowed)

Right, with all that said, i hope you guys and/or girls can understand this, because my head is a bit all over the place at the moment.

Any ideas would be REALLY REALLY appreciated 🙂

Thanks so so much,

DaveyC

[NogDog]

All I can think of is to do a check something like:

$sql = "
   SELECT COUNT(*) FROM sched_table
   WHERE
      ('$start' >= start_time AND '$start' < end_time) OR
      ('$end' > start_time AND '$end' <= end_time)
";
$result = mysql_query($sql);
$count = mysql_result($sql, 0);
if($count)
{
   // there's an overlap
}
else
{
   // no overlap
}