Select fields between 2 dates

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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.

Thanks.

John

2

John

Since 2008-03-13 and 2008-03-18 are both between 2008-03-10 and 2008-03-20, I believe both of your statements will return a result.

Maybe I am misunderstanding the question?

Ben

3

Hi Bed,

Unfortunately not. I have now condensed the 2 querys into one:

SELECT propertyid, arrival_date, departure_date FROM bookings WHERE arrival_date = ‘$arrival_date’ AND ‘$departure_date’ ) OR ( departure_date BETWEEN ‘$arrival_date’ AND ‘$departure_date’ )

which works a treat if the arrival or departure date crosses an existing booking, but it still returns an empty result if I try and add a booking 2008-03-13 to 2008-03-18.

Very strange!