需要更新MySQL查询以选择酒店房间预订或任何事情的日期范围。

Question

我是一位有用的助手，可以为您翻译文本。

我在项目中遇到了以下情况：

在检查可用房间时，

$sel_from_bookings="SELECT room_no FROM `booking` WHERE (('".$_POST['req_tdate']."' BETWEEN check_indate AND check_outdate) OR ('".$_POST['req_fdate']."' BETWEEN check_indate AND check_outdate)";

$sel_from_reserv="SELECT room_no FROM `reservation` WHERE (('".$_POST['req_tdate']."' BETWEEN check_indate AND check_outdate) OR ('".$_POST['req_fdate']."' BETWEEN check_indate AND check_outdate))"; 

$sel_rooms="SELECT room_no FROM rooms WHERE room_no NOT IN (".$sel_from_bookings.") AND room_no NOT IN (".$sel_from_reserv.")";

第一个查询从预订表中检索符合日期范围的房间号列表。

类似地，第二个查询从预约表中获取相同的内容。

最后一个查询使用上述两个查询提供的列表，并获取不在生成列表中的房间列表。

对于10-08-2010 / 15-08-2010和20-08-2010 / 25-08-2010有效。

当我给出10到15之间的日期时，它可以正常工作，同样适用于20到25之间的日期，也适用于14-08-2010和21-08-2010的日期。

但是对于16-08-2010到19-08-2010无法正常工作。

如果需要任何澄清，请问我。

谢谢。

- srinivas

---

在查询中使用$_POST时，请务必在使用之前进行过滤处理，否则会使您的网站容易受到SQL注入攻击（可能导致数据全部丢失）。 - greg0ire

@greg0ire 谢谢，我会记住的。你收到我的问题了吗？还需要澄清吗？谢谢。 - srinivas

“not working” 是什么意思？你有收到任何错误信息吗？你正在处理 MySQL 错误吗？ - greg0ire

@greg0ire 不，它没有给出任何错误消息。查询很好用，但不符合我的要求。正如我所提到的，对于日期范围16-08至19-08，查询未返回任何行，即使具有这些日期的数据存在。 - srinivas

打开你的MySQL客户端并在其中测试你的查询。如果它正常工作，请检查你正在测试的请求和你用PHP创建的请求是否相同。如果不相同，请检查MySQL语法。 - greg0ire

无法发表评论, 但是: 如果 $sel_from_bookings 或 $sel_from_reserv 为空, 那么第三个查询是否会受到影响? - zwip

Answer 1

SELECT  *
FROM    room
WHERE   room_no NOT IN
        (
        SELECT  room_no
        FROM    booking
        WHERE   check_outdate >= @req_fdate
                AND check_indate <= @red_tdate
        )
        AND room_no NOT IN
        (
        SELECT  room_no
        FROM    reservation
        WHERE   check_outdate >= @req_fdate
                AND check_indate <= @red_tdate
        )

请注意参数的顺序：@req_fdate 是第一个日期（起始日期），@req_tdate 是最后一个日期（结束日期）。

要检查从 8月16日 到 8月19日 的可用性，请使用以下代码：

SELECT  *
FROM    room
WHERE   room_no NOT IN
        (
        SELECT  room_no
        FROM    booking
        WHERE   check_outdate >= '2010-08-16'
                AND check_indate <= '2010-08-19'
        )
        AND room_no NOT IN
        (
        SELECT  room_no
        FROM    reservation
        WHERE   check_outdate >= '2010-08-16'
                AND check_indate <= '2010-08-19'
        )

- Quassnoi

Answer 2

字段check_indate和check_outdate的数据类型是什么？

我猜测BETWEEN关键字没有按预期工作。如果您将a BETWEEN b and c替换为a >= b and a <= c，是否会有不同的结果？

$sel_from_bookings="SELECT room_no FROM `booking` WHERE (('".$_POST['req_tdate']."' >= check_indate AND '".$_POST['req_tdate']."' <= check_outdate) OR ('".$_POST['req_fdate']."' >= check_indate AND '".$_POST['req_fdate']."' <= check_outdate)";

$sel_from_reserv="SELECT room_no FROM `reservation` WHERE (('".$_POST['req_tdate']."' >= check_indate AND '".$_POST['req_tdate']."' <= check_outdate) OR ('".$_POST['req_fdate']."' >= check_indate AND '".$_POST['req_fdate']."' <= check_outdate))"; 

$sel_rooms="SELECT room_no FROM rooms WHERE room_no NOT IN (".$sel_from_bookings.") AND room_no NOT IN (".$sel_from_reserv.")";

提示：您也可以尝试通过将示例转换为日期列的数据类型来进行调试。如果我们假设您的check_indate列的数据类型是datetime，则可以尝试执行以下操作以查看MySQL返回的结果：

SELECT CAST('16-08-2010' as datetime);

对比

SELECT CAST('20-08-2010' as datetime);

我的MySQL在这两个例子中都失败了，因为它期望日期格式为yyyy-mm-dd，但这可能会给你一些提示 :)

- rompetroll

Answer 3

我会看一下你用于 check_outdate 和 check_indate 的数据字段类型。

如果你使用的是“DATE”，请确保输入格式为 YYYY-MM-DD。如果你输入没有分隔符的其他任何内容，那么 MySQL 将会猜测日期。你可以使用 PHP 函数将日期格式从 DD-MM-YYYY（或其他格式）转换为 MySQL 所需的正确日期格式：

$date ='12-12-2007';
$dateTime = new DateTime($date);
$formatted_date=date_format ( $dateTime, 'Y-m-d' );
echo $formatted_date;
// This will output 2007-12-12

如果您正在使用VARCHAR或固定长度CHAR，则字符串比较可能不如预期。

- Jim Grant