php - 酒店预订 SQL 查询不起作用

Question

我有两个表:

房间

id  
category_id  
name  
description 
image  
price  
arrival_date  
depature_date  
status  

预订

id  
name  
email  
mobile  
category_id  
room_id  
arrival  
departure  

Q1。如何查询房间是否可供客户使用？
Q2。我想显示用户输入日期为空的房间列表

我正在使用这个查询，它给我error #1241 - Operand should contain 1 column(s)

SELECT * FROM room WHERE status = '1' AND id NOT IN 
    (SELECT * FROM booking
    WHERE arrival='$arrival' 
    OR ( arrival BETWEEN '$arrival' AND '$departure' ) )

SELECT * FROM room WHERE status = '1' AND id NOT IN
    (SELECT * FROM booking WHERE arrival='2/08/2013'
    OR ( arrival BETWEEN '2/08/2013' AND '22/08/2013' ))

Answer 1

主要问题是，在内部查询中，您得到的结果列太多。

尝试:

SELECT * FROM room WHERE status = '1' AND id NOT IN
    ( SELECT room_id FROM booking
    WHERE arrival='$arrival'
    OR ( arrival BETWEEN '$arrival' AND '$departure' ))

SELECT * FROM room WHERE status = '1' AND id NOT IN
    ( SELECT room_id FROM booking
    WHERE arrival='2/08/2013'
    OR ( arrival BETWEEN '2/08/2013' AND '22/08/2013' ))