我想在我的PHP代码中选择date、$checkin
和$checkout
中可用的数据。假设我的tblreservation
checkin
、checkout
和roomname
中有3列,并且已经有了4个数据。
DATA in `tblreservation`
checkin: 2018/06/14
checkout: 2018/06/21
roomname: room A
checkin: 2018/06/21
checkout: 2018/06/23
roomname: room B
checkin: 2018/06/24
checkout: 2018/06/27
roomname: room C
下面是用PHP编写的代码
$checkin = "2018/06/25";
$checkout = "2018/06/26";
$query = mysqli_query($db, "SELECT * from tblreservation where checkin > '$checkin' AND checkout > '$checkout');
"SHOULD" OUTPUT: Room A and Room B
//because the room C is already reserved, then the room a and room b will select.
在这里我试图分析这种情况,但我很困惑如何做到这一点,请帮助我走出这个问题。
发布于 2018-06-18 05:26:52
基本上,您需要首先找到该时间段内已预订的所有房间,然后再找到不在此列表中的房间。
对于第一个场景,您有3个场景:
checkin <= '$checkout' AND checkin > '$checkin'
checkout >= '$checkin' AND checkout < '$checkout'
checkin <= '$checkin' AND checkout >= '$checkout'
将这些组合在一起,我们就得到了这个语句,它给出了所有被阻塞/保留的房间:
SELECT roomID from tblreservation where
(checkin <= '$checkout' AND checkin > '$checkin')
OR (checkout >= '$checkin' AND checkout < '$checkout')
OR (checkin <= '$checkin' AND checkout >= '$checkout')
现在,让我们获取不在此列表中的所有房间:
SELECT * from rooms where
id NOT IN (
SELECT roomID from tblreservation where
(checkin <= '$checkout' AND checkin > '$checkin')
OR (checkout >= '$checkin' AND checkout < '$checkout')
OR (checkin <= '$checkin' AND checkout >= '$checkout')
)
所有这些都是由你需要调整的虚幻的表格/列名称和类型组成的。
还可以将其转换为准备好的语句。
https://stackoverflow.com/questions/50900321
复制相似问题