Room availability not functioning...someone please give guidance

silvery · March 23, 2010, 8:49pm

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

oddz · March 24, 2010, 2:42am


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.

silvery · March 24, 2010, 9:37am

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…

silvery · March 29, 2010, 6:44pm

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.


(SELECT room_no FROM room ORDER BY RAND( )LIMIT 1)

any ideas is really appreciated:)

DarthGuido · March 30, 2010, 8:08am


SELECT 
    room_no 
FROM room 
WHERE r_roomtypeID = 'the id you want to use'
ORDER BY RAND( )
LIMIT 1

silvery · April 3, 2010, 10:26am

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

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

step 1 and 2 work out well using the query below :


 (
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…


(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:



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);

IBazz · April 3, 2010, 3:21pm

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:

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:


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

IBazz · April 3, 2010, 3:24pm

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

r937 · April 3, 2010, 3:48pm

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

IBazz · April 3, 2010, 3:57pm

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

r937 · April 3, 2010, 4:11pm

maybe in your hotel, but i’m betting not in silvery’s

IBazz · April 3, 2010, 4:22pm

OK, I reckon you are right - yet again

bazz

oddz · April 3, 2010, 6:35pm

You can’t. You would select the room than update its availability.

Mittineague · April 3, 2010, 7:01pm

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”?

silvery · April 4, 2010, 12:55am

Anyhoo, you might want to consider:

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:


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

silvery · April 4, 2010, 12:58am

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?

silvery · April 4, 2010, 1:03am

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:lol:

IBazz · April 4, 2010, 1:48pm

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

silvery · April 5, 2010, 2:05am

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.

SpacePhoenix · April 5, 2010, 5:50am

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:

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