CODEWITHSUNDEEP
phpmysqlsql
user3573666
user3573666

Reputation: 27

PHP MySQL query to selecting available room in hotel booking system

currently I'm doing project for hotel booking system. Which is providing self-check-in system to user and it generates room number randomly based on roomtype selected. It sounds my project quiet weird for me but my supervisor gives me the idea to do so.

So far i've done the self check-in system and randomly generate room number

Now i'm confused in selecting available room

here is the table of room provided

dor is date of reservation or checkin date

dco is checkout date

room_num     roomtype     dor            dco
  101         Single    0000-00-00    0000-00-00
  102         Single    2014-05-29    2014-05-31
  103         Single    0000-00-00    0000-00-00
  111         Deluxe    0000-00-00    0000-00-00
  112         Deluxe    0000-00-00    0000-00-00
  113         Deluxe    2000-00-00    0000-00-00
  114         Deluxe    2014-06-01    2014-06-06
  115         Deluxe    0000-00-00    0000-00-00
  116         Deluxe    2014-06-08    2014-06-11
  121         Superior  0000-00-00    0000-00-00
  122         Superior  0000-00-00    0000-00-00

0000-00-00 means the room number not yet selected by the system. Because the room_num selected randomly by system

and below is the table room_booked. All data below comes from SQL update trigger from rooms table

room_num     roomtype     dor           dco
  102         Single    2014-05-29    2014-05-31
  114         Deluxe    2014-06-01    2014-06-06
  116         Deluxe    2014-06-08    2014-06-11

now what's the SQL code to select the available room number from room table, based on selected roomtype. Which is not at the between checkin date and checkout date mentioned on room_booked?

Thanks in advance

Upvotes: 0

Views: 10057

Answers (5)

automatix
automatix

Reputation: 14522

As I've already said in my comment, I would prefer another database structure. So I created the tables room and room_booked first

-- DROP TABLE IF EXISTS room_booked;
-- DROP TABLE IF EXISTS room;

CREATE TABLE room (
    room_num INT NOT NULL,
    roomtype ENUM('Single', 'Deluxe', 'Superior') NOT NULL,
    PRIMARY KEY (room_num)
) ENGINE=InnoDB;
CREATE TABLE room_booked(
    id INT NOT NULL,
    room_num INT NOT NULL,
    dor DATE NOT NULL,
    dco DATE NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (room_num) REFERENCES room(room_num)
) ENGINE=InnoDB;

and filled them with your original data

INSERT INTO room (room_num, roomtype) VALUES (101, 'Single');
INSERT INTO room (room_num, roomtype) VALUES (102, 'Single');
INSERT INTO room (room_num, roomtype) VALUES (103, 'Single');
INSERT INTO room (room_num, roomtype) VALUES (111, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (112, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (113, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (114, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (115, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (116, 'Deluxe');
INSERT INTO room (room_num, roomtype) VALUES (121, 'Superior');
INSERT INTO room (room_num, roomtype) VALUES (122, 'Superior');

INSERT INTO room_booked (id, room_num, dor, dco) VALUES (1, 102, '2014-05-29', '2014-05-31');
INSERT INTO room_booked (id, room_num, dor, dco) VALUES (2, 114, '2014-06-01', '2014-06-06');
INSERT INTO room_booked (id, room_num, dor, dco) VALUES (3, 116, '2014-06-08', '2014-06-11');

Now the SELECT statement. In this example the user wants to book a Deluxe room at 2014-06-01.

SELECT
    room_num, roomtype
FROM
    room
WHERE
    room_num NOT IN (
        SELECT
            room.room_num
        FROM
            room
        LEFT OUTER JOIN
            room_booked ON room_booked.room_num = room.room_num
        WHERE
            -- room type
            roomtype != 'Deluxe'
            OR (
                -- wished booking date is after or at the DOR date
                '2014-06-01' >= dor
                -- OR wished booking date is before the DCO date
                AND '2014-06-01' <  dco
            )
    )
ORDER BY
    RAND()
LIMIT 0, 1
;

If you only take the part before ORDER BY, you'll get a list of the Deluxe rooms available at 2014-06-01.

Upvotes: 2

v2solutions.com
v2solutions.com

Reputation: 1439

What I understand from your problem statement is that, you need SQL statement which will provide all currently available room for booking.

There could be two solutions for this

Solution 1

SELECT
    room_num
FROM
    room
WHERE
    room_num NOT IN(SELECT room_num FROM room_booked)
    AND roomtype = {ROOMTYPE}
ORDER BY RAND() LIMIT 1

Solution 2

SELECT
    room_num
FROM
    room
WHERE
    dor != '0000-00-00' AND dco != '0000-00-00'
    AND roomtype = {ROOMTYPE}
ORDER BY RAND() LIMIT 1

Upvotes: 0

souvickcse
souvickcse

Reputation: 7804

You have to select the room randomly and you will give the room type in mysql you can use this query 

SELECT * FROM room where roomtype='your_given_room_type' and dor='0000-00-00' and dco='0000-00-00' ORDER BY RAND() LIMIT 1

So it will pick one random room from the available rooms

Upvotes: -1

durbnpoisn
durbnpoisn

Reputation: 4659

You could try:

SELECT room_num 
from room 
where roomtype = $roomtype 
and $room_booked 
not between dor and dco

That should grab any rooms that are not currently booked between those two dates.

Upvotes: 0

CipherLock
CipherLock

Reputation: 24

It sounds like you want something like:

SELECT TOP 1 ROOM_NUM
FROM TABLE_NAME
WHERE roomtype=varRoomType AND
      dor=0000-00-00

Now I think TOP 1 is a mysql thing if your using oracle I think you have to add a rownumber=1 to the where clause. But this should get you the first found open room that matches a certain room type.

Upvotes: -1

Related Questions