Damn Tricky Date Compare Function Needed

[Anon]

Any advise or preferably code will be greatly appreciated.

I have a MySQL database which stores hotel bookings with two dates... the fromDate & the toDate (i.e. 26/12/02 - 04/01/03)

When someone wants to book a hotel room, they enter their fromDate & toDate (i.e. 02/01/03 - 09/01/03). For simplicity assume this is a single roomed hotel. The front-end then calls the checkAvailability.php script. What this script must do is check for a clash with any bookings already made.

I would assume a practical way to do this would be to create a function to recursively create an array of all the dates between the from & to date. This function could then be called, with the 'user inputted' to & from date, and cross referenced with the dates presently already in the database. To avoid inefficiently checking every booking, you may limit the SQL query to return only dates which fall on either the to &/or from month & year.

Finally, this should return a Boolean stating whether this newly inputted date clashes or not.

Good luck, this has been driving me insane so far with little success!!

Two possible useful functions:
mcal_date_valid()
mcal_date_compare()

Regards,

Justin

[merkinmuffley]

i think you can do a lot of the work in SQL by querying for all bookings in the database that have:
1) a beginning date between fromDate and toDate,
2) an ending date between fromDate and toDate, or
3) a beginning date prior to fromDate AND an ending date after endDate.

i\'ll leave that to you to translate into SQL if you wish, but the point is that in your one-room example, this gets you all bookings that overlap the requested booking.

/nick

[bastien]

a very simple way to do this would be to hard code a constant containing the number of total rooms in the hotel of each type. Then in a table store the booked rooms with dates of occupancy. then just use sql to query to count the total number of rooms booked between those dates....subtract the two and you then know if the room is available.

hth
bastien