September 8th, 2012, 04:24 PM
Select All Rooms Available for Given Date Range
I am trying to built a hotel reservation system and I have the following tables:
room_no, hotelid , type, facilities
101 | 1 etc 101 | 1 etc 101 | 1 etc
id, room_no, customerid, datefrom , dateto
1| 101 | 2 | 03-03-2012 | 06-03-2012 1| 101 | 2 | 07-03-2012 | 12-03-2012
101 | 03-03-2012
101 | 04-03-2012
101 | 05-03-2012
101 | 06-03-2012
101 | 03-04-2012
101 | 04-04-2012
101 | 05-04-2012
101 | 06-04-2012
at the availability table the owner of the hotel will store the dates that the room is available. as you see for example the room 101 is available for the dates 3-6/3/12 and 3-6/4/12. The booking table is the table tha stores the actual booking , when a room is booked it's dates will be deleted from the availability table. Now when a customer wants to search for a room available from 3/4 to 6/4/12 what query can i do to the availability table, to search all dates and see if there is a room for that date range?
September 8th, 2012, 05:02 PM
The availability table is not needed, you can use the booking table.
In booking table, you can get the occupied rooms using the start- and end-dates and then exclude them from the room list.
Code example on how it might be done: (not tested)
<insert_criteria> will be your condition where you check if the search dates overlap with the start and end date.
WHERE room_no NOT IN
Think you will learn more from it, if you give a try to make this condition yourself.
September 8th, 2012, 05:13 PM
thank you for the answer. i know it would be easier if i have no Availability table...but i want the owner of the hotel togive me same specific dates that is available and then i will make the booking.
is that possible?