SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    room availability not functioning...someone please give guidance..

    i have just create 4 tables like below :

    CREATE TABLE customer(
    customerID INT NOT NULL AUTO_INCREMENT ,
    name VARCHAR( 30 ) ,
    address VARCHAR( 30 ) ,
    tel_no INT( 15 ) ,
    email VARCHAR( 30 ) ,
    PRIMARY KEY (customerID)
    ) ENGINE=INNODB;


    CREATE TABLE roomtype(
    roomtypeID INT NOT NULL AUTO_INCREMENT ,
    roomtype VARCHAR( 30 ) ,
    roomprice INT( 30 ) ,
    roombed INT( 15 ) ,
    PRIMARY KEY ( roomtypeID )
    ) ENGINE=INNODB;

    CREATE TABLE rooms(
    roomID INT NOT NULL AUTO_INCREMENT ,
    roomtypeID varchar( 30 ) ,
    room_no INT( 15 ) ,
    PRIMARY KEY ( roomID ) ,
    FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE = INNODB


    CREATE TABLE booking(
    bookingID INT NOT NULL AUTO_INCREMENT ,
    checkin DATETIME,
    checkout DATETIME,
    nights INT( 10 ) ,
    totalprice INT( 100 ) ,
    customerID INT,
    roomID INT,
    PRIMARY KEY ( bookingID ) ,
    FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) ,
    FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE = INNODB

    i really got no idea how to only display the roomtype and roomprice from the table roomtype. I do really hope someone can help me as i have spent 5 hours only for this one query (yes..i admit i'm not talented in this stuff..), so please,if there's anyone can give any ideas for me to solve this...
    i do appreciate it so much...

    below is the query that i'm working on that never success :

    select distinct roomtype, roomprice from roomtype where romtypeID IN (
    select roomtypeID, roomID from rooms where roomID NOT IN (
    select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"))

    when i test it at phpmyadmin, the problem comes from the outter select which is the part "select distinct...".
    when i tested it, the subselect works fine..the problems comes from the select distinct part

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         rt.roomtype
         ,rt.roomprice
      FROM
         roomtype rt
     INNER 
      JOIN
         rooms r
        ON
         rt.roomtypeID = r.roomtypeID
     WHERE
         r.roomID NOT IN (
         	SELECT
         	     roomID
         	  FROM
         	     booking 
         	 WHERE 
         	     checkin >= '2010-04-01' 
         	   AND 
         	     checkout <= '2010-04-06')
     GROUP
        BY
          rt.roomtypeID

    Is there a typo on the room roomtypeID foreign key – rooms.roomtypeID is a varchar field yet references an integer column.

    It took a while before I discovered what you were actually trying to achieve. Nest time it would be helpful to others and I if you said explicitly the result set your after. For example, here you could have said all available roomtypes and everything would have been much easier to comprehend without untangling your query.

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANK YOU!! veru much oddz..
    fuh..you really saves my day.
    you query really work...


    i'm sorry about the complexity of my post as it is not clear and hard to understand. i'm quit struggling with my english here..

  4. #4
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to get random number based on the condition.

    i have two table in my database which is the room table and roomtype table.i'm using phpmyadmin.

    room table
    room_no
    r_roomtypeID


    roomtype table
    roomtypeID
    roomtype


    how can i create a query that can select A random room_no from "room table "
    based on the roomtypeID in the "table roomtype"

    p/s - each roomtypeID got it's own quantity room no.

    roomtypeID room_no

    example : Single room : 1-10
    : Deluxe room : 11-20
    : Suite room : 21-30

    right now i'm only having this kind of idea. the random number from room. I don't know how can i generate A random room_no from table room based on the roomtypeID.
    Code:
    (SELECT room_no FROM room ORDER BY RAND( )LIMIT 1)
    any ideas is really appreciated

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT 
        room_no 
    FROM room 
    WHERE r_roomtypeID = 'the id you want to use'
    ORDER BY RAND( )
    LIMIT 1

  6. #6
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to changethe status field automayically

    Hi everybody. right now i'm doing a hotel reservation system using php and phpmyadmin. The process involved

    1) user inputs check-in and check-out dates (to check what rooms are available during the dates input)
    2) database is queried for all room categories AVAILABLE within the dates indicated

    step 1 and 2 work out well using the query below :
    Code:
     (
    SELECT rt.roomtypeID, rt.roomtype, rt.roomprice
    FROM roomtype rt
    INNER JOIN room r ON rt.roomtypeID = r.r_roomtypeID
    WHERE r.room_status = 'available'
    AND r.room_no NOT
    IN (
    
    SELECT b_room_no
    FROM booking
    WHERE checkin >= '2010-04-04'
    AND checkout <= '2010-04-06'
    )
    GROUP BY rt.roomtypeID
    )
    Then a problem arise..
    How can I assign ROOM NO for any customer who has just make a reservation.

    I have a 'room table' and 'roomtype table'.
    right now when user make a reservation, i will assign them a random number based on the roomtype they had choose.

    example : roomtype Single=10 rooms, Deluxe=10 rooms, Suite=10 rooms.

    right now this is the only things that i can think right now..
    Code:
    (SELECT room_no FROM room WHERE r_roomtypeID ='single' AND room_status='available' ORDER BY RAND( ) LIMIT 1");
    And it did work out. But then, i was thinking, how can i automatically assign the status of"unavailable" for the room no that was just assign to the customer who had just make reservation.
    So, next time if another customer wanted to make a reservation, the random number that will be selected will not involved the room that has status room_status='unavailable'.

    I appreciate any ideas, keyword too google for , or any articles that i can refer to in solving this matter

    here is my database:

    Code:
    CREATE TABLE `booking` (
      `bookingID` int(11) NOT NULL auto_increment,
      `b_ic_no` varchar(30) collate latin1_general_ci NOT NULL default '',
      `b_room_no` int(11) NOT NULL default '0',
      `checkin` date default NULL,
      `checkout` date default NULL,
      `nights` int(11) default NULL,
      `totalprice` int(11) default NULL,
      PRIMARY KEY  (`bookingID`,`b_ic_no`,`b_room_no`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;
    
    --
    -- Dumping data for table `booking`
    --
    
    INSERT INTO `booking` (`bookingID`, `b_ic_no`, `b_room_no`, `checkin`, `checkout`, `nights`, `totalprice`) VALUES
    (1, '1111', 1, '2010-04-04', '2010-04-06', 2, 50),
    (2, '2222', 2, '2010-04-04', '2010-04-06', 2, 50),
    (3, '3333', 3, '2010-04-04', '2010-04-06', 2, 50),
    (4, '4444', 4, '2010-04-04', '2010-04-06', 2, 50),
    (5, '5555', 5, '2010-04-04', '2010-04-06', 2, 50),
    (6, '6666', 11, '2010-04-04', '2010-04-06', 2, 80);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `customer`
    --
    
    CREATE TABLE `customer` (
      `customer_id` int(10) NOT NULL auto_increment,
      `username` varchar(100) collate latin1_general_ci NOT NULL,
      `password` varchar(100) collate latin1_general_ci NOT NULL,
      `Name` varchar(100) collate latin1_general_ci NOT NULL,
      `ICNo` varchar(15) collate latin1_general_ci NOT NULL,
      `DOB` varchar(15) collate latin1_general_ci NOT NULL,
      `Address` varchar(100) collate latin1_general_ci NOT NULL,
      `TelNo` int(15) NOT NULL,
      `CompanyName` varchar(50) collate latin1_general_ci NOT NULL,
      `Occupation` varchar(50) collate latin1_general_ci NOT NULL,
      `Nationality` varchar(30) collate latin1_general_ci NOT NULL,
      `Email` varchar(50) collate latin1_general_ci NOT NULL,
      `level` int(4) NOT NULL default '2',
      PRIMARY KEY  (`customer_id`,`ICNo`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=20 ;
    
    --
    -- Dumping data for table `customer`
    --
    
    INSERT INTO `customer` (`customer_id`, `username`, `password`, `Name`, `ICNo`, `DOB`, `Address`, `TelNo`, `CompanyName`, `Occupation`, `Nationality`, `Email`, `level`) VALUES
    (18, 'aaa', 'aaa', 'aaa', '1111', '', 'London', 1, '', 'engineer', 'chinese', 'aaa', 2),
    (19, 'sss', 'sss', 'sss', '2222', '', 'London', 222, '', '2222', 'chinese', '2222', 2);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `room`
    --
    
    CREATE TABLE `room` (
      `room_no` int(11) NOT NULL,
      `r_roomtypeID` int(11) default NULL,
      `room_status` varchar(100) collate latin1_general_ci default NULL,
      PRIMARY KEY  (`room_no`),
      KEY `r_roomtypeID` (`r_roomtypeID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
    
    --
    -- Dumping data for table `room`
    --
    
    INSERT INTO `room` (`room_no`, `r_roomtypeID`, `room_status`) VALUES
    (1, 1, 'unavailable'),
    (2, 1, 'unavailable'),
    (3, 1, 'unavailable'),
    (4, 1, 'unavailable'),
    (5, 1, 'unavailable'),
    (6, 1, 'available'),
    (7, 1, 'available'),
    (8, 1, 'available'),
    (9, 1, 'available'),
    (10, 1, 'available'),
    (11, 2, 'unavailable'),
    (12, 2, 'available'),
    (13, 2, 'available'),
    (14, 2, 'available'),
    (15, 2, 'available'),
    (16, 2, 'available'),
    (17, 2, 'available'),
    (18, 2, 'available'),
    (19, 2, 'available'),
    (20, 2, 'available'),
    (21, 3, 'available'),
    (22, 3, 'available'),
    (23, 3, 'available'),
    (24, NULL, NULL);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `roomtype`
    --
    
    CREATE TABLE `roomtype` (
      `roomtypeID` int(11) NOT NULL auto_increment,
      `roomtype` varchar(30) collate latin1_general_ci default NULL,
      `roomprice` int(11) default NULL,
      PRIMARY KEY  (`roomtypeID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;
    
    --
    -- Dumping data for table `roomtype`
    --
    
    INSERT INTO `roomtype` (`roomtypeID`, `roomtype`, `roomprice`) VALUES
    (1, 'single', 50),
    (2, 'Twin Sharing', 80),
    (3, 'Deluxe', 100),
    (4, 'Superior', 130),
    (5, 'Suite', 150);

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In trying to understand your explanations, I think it would be easier to give you some general tips. (Hey; no criticism of your english I likely don't speak your own native language since I struggle to master english).

    Anyhoo, you might want to consider:

    1. the relationship between room numbers and room types. Can a rom provide more tha one room type? You might find this table structure works better:

    Code MySQL:
    create table room_numbers(
    room_number int primary key
    )
     
    create table room_types(
    room_type varchar (99)primary key
    )
     
    create table rooms_room_numbers
    ( room_number int
    , room_type varchar(99)
    , constraint room_numbers_fk
        foreign key (room_number)
          references room_numbers(room_number) on delete cascade on update cascade
    , constraint room_types_fk
        foreign key (room_type)
          references room_types(room_type) on delete cascade on update cascade  
    )

    That structure means that any room can offer more than one configuration - eg when a twin room can be made into a double because it has zip-n-link beds.

    Also, when a booking is made and the data for it is stored in the bookings table, you should also store the room number that that booking relates to. then you query for availability will check against the bookings table for available dates and room numbers.

    I know your pain I spent ages trying to build my reservations system and experienced mega frustration. Help was sought and received and it's all tickety-boo now


    bazz

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In post #6, your rooms table seems wrong to me. a room number shiowing as unavailabe as you have written, is shown as unavailable for ever. better to put the room number booked, into the bookings table so you know what dates it has been booked for.

    bazz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    room room room... gentlemen, start your engines...

    bazz, why do you have a room_numbers table with only a room number, and a separate "room room" numbers table which identifies the room types for a room

    i'm betting a room can be only one type, unless you call in the building contractors and do extensive renovations...

    ... in which case it won't be the same room any more, will it

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol rudy,

    a room can be sold as more than one type without getting in the builders.

    Maybe I should have named the tables as: -

    room_numbers
    room_types
    numbers_types


    bazz

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    a room can be sold as more than one type
    maybe in your hotel, but i'm betting not in silvery's

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I reckon you are right - yet again

    bazz

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by silvery
    And it did work out. But then, i was thinking, how can i automatically assign the status of"unavailable" for the room no that was just assign to the customer who had just make reservation.
    You can't. You would select the room than update its availability.

  14. #14
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,460
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    From what you posted earlier, I'm guessing the room "types" are what I would call "usage" eg.
    Rooms #217, #219, #221 - reserved by 3 parties. vs.
    Rooms #217, #219, #221 - reserved by 1 party as a "suite".

    If so, shouldn't that go into "booking" and "customer"?

  15. #15
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyhoo, you might want to consider:

    1. the relationship between room numbers and room types. Can a rom provide more tha one room type? You might find this table structure works better:

    Code MySQL:
    create table room_numbers(
    room_number int primary key
    )
     
    create table room_types(
    room_type varchar (99)primary key
    )
     
    create table rooms_room_numbers
    ( room_number int
    , room_type varchar(99)
    , constraint room_numbers_fk
        foreign key (room_number)
          references room_numbers(room_number) on delete cascade on update cascade
    , constraint room_types_fk
        foreign key (room_type)
          references room_types(room_type) on delete cascade on update cascade  
    )

    That structure means that any room can offer more than one configuration - eg when a twin room can be made into a double because it has zip-n-link beds.

    Actually, I think there's no problem with the "room" and "roomtype" table relationship and i would really wanted to keep it that way as simple as, "roomtype can have many room" and "a room is only categorized in one roomtype only"

    Also, when a booking is made and the data for it is stored in the bookings table, you should also store the room number that that booking relates to. then you query for availability will check against the bookings table for available dates and room numbers.
    Yes. i did put the room no that have been booked into the "booking table" by using the 'b_room_no'..


    bazz

  16. #16
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    You can't. You would select the room than update its availability.
    I'm sorry oddz. Of course i would select the room and at the same time, i should also update the room availability after a room is assign to the customer.
    Isn't that this is the right method/procedure?

  17. #17
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    From what you posted earlier, I'm guessing the room "types" are what I would call "usage" eg.
    Rooms #217, #219, #221 - reserved by 3 parties. vs.
    Rooms #217, #219, #221 - reserved by 1 party as a "suite".

    If so, shouldn't that go into "booking" and "customer"?
    roomtype is the room types that available in the Hotel.I don't put roomtype into the booking and customer since i think that room_no is enough already to relates between booking that have been made by customer.

    The relationship is like :
    > table customer have room no > table room no have roomtype > roomtype have it's own ID.

    i'm sorry if my english is bad or it makes anyone couldn't understand what i'm trying to state

  18. #18
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by silvery View Post
    Of course i would select the room and at the same time, i should also update the room availability after a room is assign to the customer.
    Isn't that this is the right method/procedure?
    I would simply show your occupancy in the bookings table so that when you search for availability you check the bookings table for dates of a booking, which do not overlap the dates chosen.

    If you show room 1 as booked, in the rooms table, how does the booking engine know when it is unavailable? Think of it another way.

    Is the booking status of a room, relative to the room number? Is the relationship based on dates? If you agree that the relationship based on dates, then the status (available/booked), should be calculated by checking the bookings table for check-in dates and check-out dates and look for those which do not overlap the requested dates. Basically, you rely on the check-in-date and check-out-date in the bookings table, to show when a room is booked.

    my 2c.


    bazz

  19. #19
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you show room 1 as booked, in the rooms table, how does the booking engine know when it is unavailable? Think of it another way.

    actually this is the problems i'm facing right now. How to make the booking engine knows when it is unavailable or not.

  20. #20
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    I would assume that the availability would be changed by the user if the room was taken out of action for say refurbishment. To assign a customer an available of room of the type that has been requested, between the dates they have requested the room number inserted would have to be inserted by a sub-query:

    Code SQL:
    INSERT INTO
        booking
    VALUES
        (NULL,5555,(
            SELECT
                room.room_no AS room_number
            FROM
                room
            INNER JOIN
                booking AS reservation
            ON
                room.room_no = reservation.b_room_no
            WHERE
                NOT (
                            reservation.checkin >='2010-04-26'
                        AND
                            reservation.checkin <='2010-05-27'
                    )
            AND
                NOT (        reservation.checkout >='2010-04-26'
                        AND
                            reservation.checkout <='2010-05-27'
                    )
            AND
                room.r_roomtypeID =1
            AND
                room.room_status = 'available'
            ORDER BY
                RAND( )
            LIMIT
                1)
            ,'2010-04-24','2010-04-27',5,250)

    That query works in as much as it gets an available room number but when next run it doesn't seem to take any notice of the date inserted (which is probably just down to my own in-experience with working with dates and SQL. In any case it shows how to get to room number for the main query from the sub-query.

    What's meant to happen with the query is that the sub-query is supposed to get a list of rooms of the type requested which don't have any existing bookings assigned between the the dates and including the dates specified. It also checks to see if the room is available. Once it has a list of the available rooms it chooses one which the main query takes and uses for the booking details that get added to the bookings table.

    I hope that helps a bit
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  21. #21
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course there are various ways to do it, I'm sure. But if you treat all bookings and rooms closures as plain old bookings (just that they are booked to the owner if closed), then the status of a room will always be consistently in the bookings table and you won't have any use for availability in the rooms table.

    If an availability check is made and you query the bookings table using SpacePhoenix's where clause or similar (I didn't check its detail), you can check if there is a room number, which is not booked on or between the dates you want and, which therefore is neither closed nor booked. That means it would be available.

    hth

    bazz

  22. #22
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll post some faux/pseudo code to help explain what I meant there:
    Code MySQL:
    select
            room number
          , room_type
      FROM rooms AS r
    INNER
       JOIN room_types AS rt
        ON r.room_number = rt.room_number
      AND rt.room_type = 'room_type_sought'
    and NOT EXISTS (
                         SELECT b.room_number
                         FROM booked AS b
                         WHERE b.room_number = rt.room_number
                         and b.check_out_date >= ?   # arrival_requested
                         and b.check_in_date <=  ?    # departure_requested
                           )  
     
    WHERE -- other conditions here --

    bazz


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
  •