需要更新mysql查询以选择日期范围以预订酒店房间或任何东西 [英] need updation for mysql query for choosing date range for reservation of a hotel room or any thing

问题描述

我在项目中遇到的情况如下.

i had a situation in my project that is as follows.

在检查可用房间时

$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.")";

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

The first query retrives the list of room numbers from the booking table which satisfies the daterange

类似地，第二个在表保留中的作用相同

similarly the second one dos same from the table reservation

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

the last query uses the list provided by the above two queries and gets the list of room which are not in the generated list.

在2010年10月8日/2010年8月15日可以正常运行

works fine for 10-08-2010 / 15-08-2010

在2010年8月20日/2010年5月25日可以正常运行

works fine for 20-08-2010 / 25-08-2010

当我给出10到15之间的日期时，它对20到25的效果一样好，并且对于14-08-2010和21-08-2010的日期也很好

when i give the dates between 10 and 15 it works fine similarly for 20 and 25 and also works fine for the dates 14-08-2010 and 21-08-2010

但不适用于2010年6月16日至2010年8月19日

but not working for 16-08-2010 to 19-08-2010

需要任何澄清，请问我.

need any clarification please ask me.

谢谢.

推荐答案

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是最后一个日期(直到).

Pay attention to the order or the arguments: @req_fdate here is the first date here (from), @req_tdate is the last date (till).

要检查从Aug 16到Aug 19的可用性，请使用以下命令:

To check for availability from Aug 16 to Aug 19, use this:

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'
        )

这篇关于需要更新mysql查询以选择日期范围以预订酒店房间或任何东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！