Jump to content

[SOLVED] Select fields between 2 dates


johnadamson

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/97991-solved-select-fields-between-2-dates/
Share on other sites

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 )

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

  • 2 weeks later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.