在MySQL中检查日期范围冲突 [英] Checking for date range conflicts in MySQL

问题描述

我正在写一个酒店预订系统.经过大量研究(包括堆栈溢出)，我编写了此sql来查找免费房间:

I am writing a hotel booking system. after lots of studying (including stack overflow) i wrote this sql to find out free rooms:

SELECT
*
FROM room
WHERE
    room_id NOT IN (
        SELECT room_id
        FROM bookings
        WHERE
                 checkin <= '$check_in'
            AND checkout >= '$check_out'
    )

但是问题是它没有考虑时间检查是12:00:00和结帐时间是11:59:00

but the problem is its not considering the time checking is 12:00:00 and checkout is 11:59:00

它在日期范围内也无法给出正确的查询，就像我从15-18预定一个单人房的房间是501一样.如果我再次运行查询17-19，这个房间似乎是免费的，但实际上它应该被占用.

also its not giving the right queries like within the date range its not working like if I book from 15-18 a single room who's number is 501. if I again run a query 17-19 this rooms seems free but in reality it should be occupied.

任何人都可以建议一个非常有效的sql来获取准确的日期，这样预订系统就不会发生冲突，因为该系统将在实际中实施，因此错误会导致很多问题.

can anyone suggest a very good and effective sql which will get the accurate date so that no clash will happen booking system because the system will be implemented in real so mistakes will cause many issues.

预先感谢

推荐答案

您遇到的问题是查询不够可靠.当您解决问题时，您所拥有的是这样的:

The problem you're having is that your query is not sufficiently robust. When you break down the problem, what you have is this:

如果$check_in和$check_out定义的范围以 any 方式与checkin和checkout定义的范围重叠，则预订房间.否则，它是免费的.

If the range defined by $check_in and $check_out overlaps the range defined by checkin and checkout in any way, then the room is booked. Otherwise, it is free.

这意味着:

• 如果$check_in> = checkin并且$check_in< = checkout，则该房间为 BOOKED
• 或如果$check_out> = checkin和$check_out< = checkout，则该房间为 BOOKED
• 或如果$check_in< = checkin和$check_out> = checkout，则该房间为 BOOKED

• If $check_in >= checkin and $check_in <= checkout, the room is BOOKED
• OR If $check_out >= checkin and $check_out <= checkout, the room is BOOKED
• OR If $check_in <= checkin and $check_out >= checkout, the room is BOOKED

因此，您需要在子查询中代表这两种情况，以获得所需的信息.

So, you need to represent both of these scenarios in your subquery in order to get the information you're looking for.

此外，希望您将使用datetime进行比较，而不仅仅是time，否则会产生副作用.

Also, you will hopefully be using datetime for your comparisons and not just time, otherwise you will have side effects.

SQL查询

(请记住，可以说，给猫剥皮的方法不止一种.我只是提供一个示例，尽可能地保留您已有的猫.我再次假设checkin，checkout，$check_in和$check_out都将解析为datetime类型)

(Keep in mind that there is more than one way to skin a cat, so to speak. I'm just providing an example that keeps with what you already have as much as possible. Once again, I'm also assuming that checkin, checkout, $check_in, and $check_out will all resolve to datetime types)

SELECT *
FROM room
WHERE room_id NOT IN
(SELECT room_id 
 FROM bookings
 WHERE
   (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屋！