#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Smile Select All Rooms Available for Given Date Range


    I am trying to built a hotel reservation system and I have the following tables:

    hotel
    id, etc
    customer
    id, etc

    room
    room_no, hotelid , type, facilities
    101 | 1 etc 101 | 1 etc 101 | 1 etc

    booking
    id, room_no, customerid, datefrom , dateto
    1| 101 | 2 | 03-03-2012 | 06-03-2012 1| 101 | 2 | 07-03-2012 | 12-03-2012

    availability
    room_no, date
    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?
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,643
    Rep Power
    1945
    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)
    Code:
    SELECT room_no
    FROM room
    WHERE room_no NOT IN 
    	(SELECT room_no 
    	 FROM booking
    	 WHERE <insert_criteria>
    	)
    <insert_criteria> will be your condition where you check if the search dates overlap with the start and end date.
    Think you will learn more from it, if you give a try to make this condition yourself.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    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?

IMN logo majestic logo threadwatch logo seochat tools logo