PHP - Check if event is overlapping another one

Question

I'm currently developping an event management system for my client but I'm stuck at one place.

For each event, we need to specify the local where the event is happening. This should also be verified.

I need to be able to add an event and check before saving if the event is not overlapping another one in the DB. For example, I got those event in my DB:

+------------+------------+------------+----------+----------+
| EVENT NAME |    DATE    | BEGIN_TIME | END_TIME | LOCAL_ID |
+------------+------------+------------+----------+----------+
| Test 1     | 12/11/2013 | 11:00:00   | 12:00:00 | 250      |
| Test 2     | 12/11/2013 | 10:30:00   | 11:30:00 | 150      |
+------------+------------+------------+----------+----------+

Now, if I want to add an event from 11:00:00 to 12:00:00 in local 150, it should give me an error because there's already an event from 10:30 to 11:30 which is in the same time of the other event. BUT, if I add the same event but for the local 200 (for example), it should work because there's no other event at the same time in the local...

I tried to calculate time and date using PHP range() and using array methods, but it didn't work. I don't know how I can check that.

Note: I'm using Joomla! 3.1 and I'm able to write my own plugin if needed.

Answer 1

You can check via SQL - Execute a query to check for any event, matching the local_id, that have a events scheduled between the proposed dates.

For example

 SELECT 
  1  
 FROM 
  events 
 WHERE 
  `local_id` = :local_id 
 AND 
  `date` = :date 
 AND 
  (
    (`begin_time` BETWEEN (:start) AND (:end)
    OR
    (`end_time` BETWEEN (:start) AND (:end)
  )

If you get any results then you know that there was a matching event