MySQL query for checking event conflict

Currently I making a school community site and I got a problem in a part were the administrator post a new event he/she wants check if the event start date and time has no conflict also end date and time before saving it on the database. I tried many queries still i fa开发者_运维技巧il to get the exact sql.

I'm using MySQL Server 5 and PHP 5.3. Can any one help me on how to query on the table i mean the right sql string.

Heres the table fields:

• event_id => int,
• event_type => enum('school','department'),
• event_title=> text,
• event_details=> longtext,
• event_start=> datetime,
• event_end=> datetime,
• event_added_by=> int,
• event_modified_on=> datetime,

Here is what i have right now:

 SELECT *, time(event_start) as start_time, time(event_end) as end_time
 FROM `tbl_events` WHERE ((date(event_start) between '2011-12-9' and
 '2011-12-15') AND (time(event_start) between '00:00:00' and
 '01:00:00')) OR ((date(event_end) between '2011-12-9' and
 '2011-12-15') AND

---

You have four things to look for that indicate a conflict:

1. an existing event starts before your event starts and ends after your event ends (your event is inside the existing event)
2. an existing event starts before your event starts and ends after your event starts (overlap the beginning of the new event)
3. an existing event starts before your event ends and ends after your event ends (overlap the end of the new event)
4. an existing event starts after your event starts and ends before your event ends (the existing event is inside the new event)

By before and after, I mean at the same time or before/after (<= and >=).

---

There are two checks to do that need to be combined into one condition:

• an (existing) event starts before your event ends

  and

• it ends after your event starts.

If the query with such a condition returns rows, there's a conflict.

I'm assuming here that the end time is the time when the event ceases to be active (or functional or valid or whatever). That is, if an event's start time equals another event's end time, there's no conflict in there. However, if that should be considered a conflict in your case, replace the before and after above with before or when and after or when respectively.

Here's how it might look in a query:

…
WHERE event_start < @new_event_end
  AND event_end > @new_event_start
…

Or, if no event_start should match another event's event_end:

…
WHERE event_start <= @new_event_end
  AND event_end >= @new_event_start
…

(That is, simply replace strict inequalities with non-strict ones.)

---

This allows also for open ends (modeled by end = null):

WHERE (event2.start < event1.end OR event1.end is null)
AND (event2.end > event1.start OR event2.end is null)

And take care whether using > or >= (respectively < or <=), this depends on your data and requirements.