user3573666 user3573666 - 4 months ago 22
PHP Question

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

Answer

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.