SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    joining 4 tables

    Hi

    I am trying to build search query for hotel rooms availability but it seems that his query is way over my head, and i need
    help to build it. Note that there will be multiply hotels in the database.

    Even that I am looking for available rooms, my idea was not to build availability table, but instead to use reservation
    table, and I assume that if the room is not in the reservation table, it is available. (If you have any suggestions about this solution
    I would like to hear it)

    I have the following fields in the search form:

    area (represented with areaid), checkInDate, checkOutDate, rooms (how many rooms he need), adults and children's.

    Here are the tables that should be involved in this search:

    room
    roomType
    reservationroom
    hotels

    Here are the tables:

    Code:
    CREATE TABLE `room` (
      `roomID` int(11) NOT NULL AUTO_INCREMENT,
      `hotelID` int(11) NOT NULL,
      `roomtypeID` int(11) NOT NULL,
      `roomNumber` int(11) NOT NULL,
      `roomName` varchar(255) NOT NULL,
      `roomDescription` text,
      `roomVisible` tinyint(4) NOT NULL,
      PRIMARY KEY (`roomID`),
    ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `roomtype` (
      `roomtypeID` int(11) NOT NULL AUTO_INCREMENT,
      `hotelID` int(11) NOT NULL,
      `roomtypeName` varchar(255) NOT NULL,
      `roomtypeAdults` int(11) NOT NULL,
      `roomtypeChildrens` int(11) NOT NULL,
      `roomtypeDescription` text,
      PRIMARY KEY (`roomtypeID`),
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `hotel` (
      `hotelID` int(11) NOT NULL AUTO_INCREMENT,
      `areaID` int(11) NOT NULL,
      `hotelcategoryID` int(11) DEFAULT NULL,
      `hotelName` varchar(255) NOT NULL,
      `hotelShortDescription` text,
      `hotelAddress` varchar(255) DEFAULT NULL
      PRIMARY KEY (`hotelID`),
    ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
    
    
    CREATE TABLE `reservationroom` (
      `reservationroomID` int(11) NOT NULL AUTO_INCREMENT,
      `reservationID` int(11) NOT NULL,
      `hotelID` int(11) NOT NULL,
      `roomID` int(11) NOT NULL,
      PRIMARY KEY (`reservationroomID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=greek;
    Please note that I have removed the unnecessery fields from the tables, to make the code shorter and more easier to read.

    Can anyone point me how to build this query to find available rooms? While I do know how to make simple join, this query is way over my head, and any help will be deeply appreciated.

    Regards, zoreli

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by zoreli View Post
    ... use reservation table, and I assume that if the room is not in the reservation table, it is available.
    this is the best way to do it, and it's not that hard

    you query the existing reservations for the time span desired, and anything that comes up is not available, so you can go ahead and book any of the rooms that didn't come up

    querying for overlaps has been discussed here several times (search the forum for "timeline")

    here's one example -- http://www.sitepoint.com/forums/show...ange-under-SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is the best way to do it, and it's not that hard

    you query the existing reservations for the time span desired, and anything that comes up is not available, so you can go ahead and book any of the rooms that didn't come up

    querying for overlaps has been discussed here several times (search the forum for "timeline")

    here's one example -- http://www.sitepoint.com/forums/show...ange-under-SQL
    Thanks for your reply.

    The pointed example was too much complcated for me to understand.

    So far, I come out with following solution, but I will need to add some more tables here

    Code:
     SELECT r.* FROM room r 
    LEFT JOIN `reservationroom` rr 
    ON r.`hotelID` = rr.`hotelID`
     AND r.`roomID` = rr.`roomID`
     WHERE ( rr.`reservationroomID` = '' OR rr.`reservationroomID` IS  NULL );
    I am posting the solution here in hope that it may be valuable for someone else who have the similar problem. It is not final solution however.

    Regards, zoreli

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2005
    Posts
    1,850
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by zoreli View Post
    I assume that if the room is not in the reservation table, it is available. (If you have any suggestions about this solution
    I would like to hear it)
    That also assumes the room is not unavailable for other reasons such as remodeling, maintenance, fumigating for bed bugs, being taken temporarily out of service, etc.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •