I am trying to develop a booking system in PHP with MySQL but I am having trouble writing some code that checks to see if a new booking clashes with another. e.g. in the database I have a booking that runs from 2008-03-10 to 2008-03-20. When I add a booking I want to check to see if a booking already exists between these dates. Is this possible?
I am not an expert with MySQL but the closest I got is to carry out 2 query’s:
SELECT propertyid, arrival_date, departure_date FROM bookings WHERE arrival_date BETWEEN ‘$arrival_date’ AND ‘$departure_date’
SELECT propertyid, arrival_date, departure_date FROM bookings WHERE departure_date BETWEEN ‘$arrival_date’ AND ‘$departure_date’
but this obviously will not check if a booking is made in between these dates, e.g. if I try and add a booking from 2008-03-13 to 2008-03-18 it will not think that there is a collision of booking.
I hope I have made sence and not confused anyone.
Your help would be greatly appreciated.