php - 使用这些表的房间阻塞

Question

我有以下这些数据库表

预订表

.---------------+-----------------+------------------+---------------.
| reservationID |    guestID      |     check_in     |   check_out   |
+---------------+-----------------+------------------+---------------+
|       1       |       1         |     2016-02-13   |  2016-02-14   |
|       2       |       1         |     2016-02-16   |  2016-02-19   |
.---------------+-----------------+------------------+---------------.

房间预订表

.---------------+-----------------+------------------+---------------+--------------.
| roomReserveID | reservationID   |  date_scheduled  | roomDetailsNo |   roomNo     |
+---------------+-----------------+------------------+---------------+--------------+
|       1       |       1         |     2016-02-13   |      2        |      101     |
|       2       |       1         |     2016-02-13   |      2        |      101     |
|       3       |       4         |     2016-02-16   |      6        |      116     |
|       4       |       4         |     2016-02-17   |      6        |      116     |
|       5       |       4         |     2016-02-18   |      6        |      116     |
|       6       |       4         |     2016-02-19   |      6        |      116     |
.---------------+-----------------+------------------+---------------+--------------.

待定房间

.---------+-----------------+-----------.
| RoomID  | RoomDetailsNo   |room_status| 
+---------+-----------------+-----------+
|   101   |         1       | reserved  |
|   102   |         3       | available |
|   103   |         3       | available |
|   104   |         2       | reserved  |
|   105   |         2       | available |
|   116   |         6       | reserved  |
.---------+-----------------+-----------.

房间详情待定

.----------------+----------------+---------.
| RoomDetailsNo  |   room_type    |room_rate|
+----------------+----------------+---------+
|        1       | Standard Room  |  3000   |
|        2       | Deluxe Room    |  3500   |
|        3       | Family Room    |  4000   |
|        4       | Standard Quad  |  4000   |
|        5       | Deluxe Quad    |  4000   |
|        6       | Dormitory Room |  4000   |
.----------------+----------------+---------.

下面我有当前查询，我正在使用它来阻止 2016-02-13 到 2016-02-14 的房间 101 和 2016-02-16 到 2016-02-19 的房间 116。

$roomBlock = mysqli_query($conn, "
SELECT COUNT(r.roomDetailsNo) AS available_rooms, rd.roomDetailsNo, rd.room_type, rd.room_rate,

    r.room_status, rd.room_pax, rd.max_cap, rd.inclusive, rd.description, rd.image_name
    FROM rooms AS r
    LEFT JOIN roomdetails AS rd
    ON r.roomDetailsNo = rd.roomDetailsNo
    WHERE r.roomNo  = (SELECT rr.roomNo
                        FROM roomreservation AS rr
                        WHERE rr.date_scheduled NOT BETWEEN '$newCheckIn' AND '$newCheckOut')
    GROUP BY room_type;
") OR die("Error in roomBlocking:".mysqli_error($conn));

变量 $newCheckIn 和 $newCheckOut 来自用户，它们是他们期望的入住和退房日期。如何不显示在特定日期的房间预订中预订的房间？

当我尝试这个查询时，结果是:roomBlocking 错误:子查询返回多于 1 行

Answer 1

您的子查询返回的结果超过 1 个，这是预期的，因此您应该使用 where r.roomNo in，而不是 where r.roomNo =