PHP MySQL查询选择酒店预订系统中的可用房间

Question

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 1

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.