[SOLVED] Select fields between 2 dates

[johnadamson]

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'

and

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

[fenway]

You can't use between for this... you have to check them separately.

[johnadamson]

okay. so how do I check to see if a booking has clashed between the 2 dates? even if I was to do it seperately I still wouldnt know how to go about doing it?

[fenway]

sorry, I'm a bit out of it today... you can simply combine both of those where clause with an OR.

 

WHERE ( arrival_date BETWEEN '$arrival_date' AND '$departure_date' ) OR ( departure_date BETWEEN '$arrival_date' AND '$departure_date )

[johnadamson]

that worked a treat!!

 

so is there no way I can check between these 2 dates? e.g. if I try and add a booking from 2008-03-13 to 2008-03-18 it thinks that there is a collision of bookings.

[johnadamson]

sorry, meant to say that it thinks there is NO collision of bookings! surely there must be a query that checks to see if the new arrival and departure date is not in the database??

 

I also tried doing:

 

SELECT propertyid, arrival_date, departure_date FROM bookings WHERE ( arrival_date BETWEEN '$arrival_date' AND '$departure_date' ) OR ( departure_date BETWEEN '$arrival_date' AND '$departure_date' ) AND (arrival_date >= '$arrival_date' AND departure_date <= '$departure_date')

 

but that didnt work either.

 

HELP

[Barand]

see

 

http://www.phpfreaks.com/forums/index.php/topic,183811.msg822544.html#msg822544

[fenway]

Yeah, that... you have to check them "sequentially"... if the start is between, then make sure end isn't, etc.

[Barand]

you may find this thread useful too

 

http://www.phpfreaks.com/forums/index.php/topic,172418.0.html

[johnadamson]

Barand, that worked fantastic!! Thanks for you help everyone!!!