MySQL查询预订.如何在同一日期允许两次预订(入住/退房) [英] MySQL query for booking. How to allow two reservations on the same date (check-in/check-out)

问题描述

我正在尝试预订两个日期之间的所有房间.
我现在正在使用此查询，但是我遇到了问题.我尝试了很多事情，做了很多搜索，但是几个小时后，我决定尝试在这里寻求帮助.

I'm trying to get all the room booked between two dates.
I'm using this query right now but I'm having an issue with it. I tried many thing, did a lot of search but after some hours I deciced to try to ask here for help.

SELECT 1 FROM reservations WHERE
checkin <= '$check_out' AND checkout >= '$check_in'

假设某人从21日到22日预订.
他于21日的4:00 PM签入，并于22日的12:00 PM签出
该房间在4:00 PM之前的21日仍然免费.
下午12:00以后的22号房间仍然免费

Let's say someone book 21st to 22nd.
He check-in on 21st at 4:00PM and check-out on 22nd at 12:00PM
The room is still free on 21st before 4:00PM
The room is still free on 22nd after 12:00PM

使用此查询，我无法预定20日至21日
在4:00 PM时签入20日，在12:00 PM时签出21日

With this query, I can't book 20th to 21st
Check-in 20th at 4:00PM, check-out 21st at 12:00PM

我也无法预定22日至23日.

I also can't book 22nd to 23rd.

有什么主意如何调整查询以反映现实情况?

Any idea how to adjust the query to reflect that reality?

还有该查询:

(checkin <= '$check_in' AND checkout >= '$check_in') OR
(checkin <= '$check_out' AND checkout >= '$check_out') OR
(checkin >= '$check_in' AND checkout <= '$check_out')

您可以参考这个问题，这是我进行查询的地方: 在MySQL中检查日期范围冲突

You can refer to this question, this is where I took my queries: Checking for date range conflicts in MySQL

非常感谢！

推荐答案

@georstef向我发布了此链接，以查看它对我的问题下方的评论是否有帮助:

@georstef posted me this link to see if it helps in the comment below my question: Using MySQL query in selecting room availability

做到了！

现在正确的WHERE子句如下:

The correct WHERE clause is now the following:

(checkin <= '$check_in' AND checkout >= '$check_in') OR
(checkin < '$check_out' AND checkout >= '$check_out') OR
(checkin >= '$check_in' AND checkout < '$check_out')

谢谢！

这篇关于MySQL查询预订.如何在同一日期允许两次预订(入住/退房)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！