Database SQL help needed to check date range clashes!

Hi everyone, I am working on a company intranet and I'm having some trouble implementing a constraint via an SQL statement. Hope you guys could help? The scenario is written below:

A person creates a holiday record by entering a date from and date to. For example 5-jan-2012 to 8-jan-2012. This goes through fine but being a bit silly they have also added another record 7-jan-2012 to 15-jan-2012.

As you can see from the above scenario there is a clash, meaning they would be wasting their own company holidays!

Is there any chance someone could help edit my SQL statement to stop clashes such as that occurring?

SQL statement currently used:

Code:

---

$sql =
"INSERT into holidays (employee_id, date_from, date_to, total_days, status)
VALUES
(:employee_id, :date_from, :date_to, :total_days, 0)";

---

Any help would be greatly appreciated!

You have to check before inserting new record if new start or end date is in between existing start and end date.

Do you mean with another SQL statement? If so how could I do that?

One way to solve this issue is defining check constraints for the column.

Hi thanks for your reply, any chance of giving me an example of how to do it?

Which rdbms do you use?

Well I'm using MAMP which comes with phpmyadmin which involves MYSQL. Thanks by the way!

You may find that in MySql documentation.