Check overlapping period (between dates) by My-SQL or PHP

Question

I need some logic on this one..

I have a My-SQL table as follows

id | program_name | start_date | end_date
 1 | program1     | 2014-12-01 | 2014-12-07

Now if I want to insert a new record by PHP for id 1 I want to check the period (between start_date and end_date) overlaps or no..

Lets say I want to insert a new record with

id = 1 , program_name = program1 , start_date = 2014-12-06 , end_date = 2014-12-10

As the period overlaps here I want to throw a warning message. I need logic for that.

What I have come up is,

Let 

start_date in database = old_start_date , end_date in database = old_end_date, the insert record start date = new_start_date , insert record end date = new_end_date

if

old_start_date < new_start_date and old_end_date < new_end_date == fine  (period before the entered period)
old_start_date > new_start_date and old_end_date > new_end_date == fine  (period after the entered period)
old_start_date < new_start_date and old_end_date > new_end_date == warning (old period is within the new period)
old_start_date > new_start_date and old_end_date < new_end_date == warning (new period is within the old period)
old_start_date = new_start_date == warning
old_end_date = new_end_date == warning

Please guide me if I am wrong or would there be a more efficient way of doing this either by My-SQL or by PHP.

Thanks.

Answer 1

Here is the logical sequence in witch you should work: (Once you understand it, you can use it in any programming language)

1- Create function_a(date d) that returns true if a date is bigger than a specific date

2- Create function_b(date d) that returns true if a date is smaller than a specific date

3- Create a loop and call function_a (call it on all old_end_dates) and make it return true only if all results return true (Save result in var_a)
Generally speaking: only if the new start date is bigger than all records end dates , return true

4- Create a loop and call function_b (call it on all old_end_dates) and make it return true only if all results return true (Save result in var_b)
Generally speaking: only if the new end date is smaller than all records end dates , return true

5- If (var_a==true or var_b ==true ) -----> Then it is safe to save the new record, because if the new start date is after an old end date , or the new end date is before the old start date then they cannot overlap.