how to make sure no time overlap for events

[jiehuang001]

I have a table "events" which has columns "start_time", "end_time" of datatype "time" which stores value such as "11:30:00". The rule is that no event should have a overlap time. So, if for one day there is already an event from 8AM to 10AM, then a new event could not start earlier than 10AM. I think the following code should be able to do this checking, but it didn't work. Can someone please help me correct it.

$input_day = $POST["input_day"];
$input_start_time = $POST["input_start_time"]; //a string like "9:00:00"
$input_end_time = $_POST["input_end_time"]; //a string like "10:00:00"

$sql = "SELECT start_time, end_time from events where the_day = $input_day";

$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
$existing_event_start_time = row["start_time"];
$existing_event_end_time = row["end_time"];
if ($input_start_time > $existing_event_end_time || $input_end_time > $existing_event_start-time) {
print "sorry, this time slot already taken";
//code to send user back to the input page
}
}

[Rodders]

I had a similar problem a while ago and I managed to work a solution......

It may be possible in a single query, but I think my solution was to use separate queries for each overlap possibility - i.e;

new start time is between current start and end time
new end time is between current start and end time
current start time is between new start and end time
current end time is between new start and end time

Then I used PHP to sort out what happened

function lesson_clash ($new_start_time, $new_end_time) {

// test 1
// SELECT where $new_start_time between current start/end

if (mysql_num_rows($result) > 0) {
 return True;
}

// test 2......

// test 3......

// test 4......

}


if (lesson_clash($start, $end) {
echo "The lesson clashes !!";
}
else {
echo "The lesson is OK";
}

Hope you can tweak that to your needs