需要更新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屋!