mysql检查特定日期和持续时间的酒店房间可用性 [英] mysql to check room availability in a hotel for a particular date and time duration

问题描述

我需要帮助来查找查询，以检查特定日期和时间段内酒店房间的可用性. 我在下面解释.

I need help to find out a query to check the availability of rooms in a hotel for a particular date and time duration. I am explaining it below.

表"hotel_room_book"具有以下列.

Table "hotel_room_book" has the folloing columns.

CREATE TABLE IF NOT EXISTS `hotel_room_book` (
  `hotel_room_book_id` int(10) NOT NULL AUTO_INCREMENT,
  `hotel_room_id` int(10) NOT NULL,
  `hotel_id` int(10) NOT NULL,
  `who_has_booked` int(10) NOT NULL,
  `booked_for_whom` int(10) NOT NULL,
  `room_price` float(10,2) NOT NULL,
  `book_status` enum('0','1') NOT NULL DEFAULT '0',
  `booking_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `checkin_date` date NOT NULL,
  `checkin_time` int(4) NOT NULL,
  `checkout_date` date NOT NULL,
  `checkout_time` int(4) NOT NULL,
  PRIMARY KEY (`hotel_room_book_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

插入hotel_room_book (hotel_room_book_id， hotel_room_id，hotel_id， who_has_booked，booked_for_whom， room_price，book_status， booking_date，checkin_date， checkin_time，checkout_date， checkout_time)值(1、13、1、1， 1，564564.00，'0'，'2011-06-15 00:00:00'，'2011-06-15'，3， '2011-06-17'，12)，(2，13，1，1，1，1， 564564.00，'0'，'2011-06-15 00:00:00'，'2011-06-17'，16，'2011-06-18'，3)， (3，13，1，1，1，23.00，'0'， '2011-06-01 00:00:00'，'2011-06-19'， 5，'2011-06-20'，18);

INSERT INTO hotel_room_book (hotel_room_book_id, hotel_room_id, hotel_id, who_has_booked, booked_for_whom, room_price, book_status, booking_date, checkin_date, checkin_time, checkout_date, checkout_time) VALUES (1, 13, 1, 1, 1, 564564.00, '0', '2011-06-15 00:00:00', '2011-06-15', 3, '2011-06-17', 12), (2, 13, 1, 1, 1, 564564.00, '0', '2011-06-15 00:00:00', '2011-06-17', 16, '2011-06-18', 3), (3, 13, 1, 1, 1, 23.00, '0', '2011-06-01 00:00:00', '2011-06-19', 5, '2011-06-20', 18);

已预订room_id 13

Means room_id 13 is booked for

slno     checkin_date     checkin_time     checkout_date   checkout_time

 1        15-06-2011      3                 17-06-2011      12

 2        17-06-2011      16                18-06-2011      3

 3        19-06-2011      5                 20-06-2011      18

我正在搜索

slno     checkin_date     checkin_time     checkout_date     checkout_time

1         17-06-2011       13                    17-06-2011       15

2        18-06-2011        4                     19-06-2011       4

3        14-06-2011        2                     15-06-2011       1

4        20-06-2011        19                    21-06-2011       2

在有空"情况下，我使用了以下逻辑:

I used the following logic for "room available" cases:

情况1 :(以上1,2,3) 有空房间

case1:(1,2,3 in above) Room is available

如果需要，checkin_date和checkin_time> =已预订checkout_date和checkout_time

if required checkin_date and checkin_time >= booked checkout_date and checkout_time

以及必需的checkout_date和checkout_time< =已预订的checkin_date和checkint_time

and required checkout_date and checkout_time <= booked checkin_date and checkint_time

情况2 :(以上要求的日期中的4个匹配可用性) 房间可用

case2:(4 in above required date matches for availability) Room is available

如果需要，checkin_date和checkin_time< =已预订checkin_date和checkin_time

if required checkin_date and checkin_time <= booked checkin_date and checkin_time

以及必需的checkout_date和checkout_time< =已预订的checkin_date和checkint_time

and required checkout_date and checkout_time <= booked checkin_date and checkint_time

如果我手动检查，我的查询是正确的，但得到的记录为零，这是显而易见的. 因此，您能否考虑一下并帮助我找出在特定期间内特定房间的可用/不可用二进制结果为是/否或1/0的查询?

My query is correct if I check manually ,but I get zero record which is obvious. Hence can you please think on it and help me to find out the query which gives binary result yes/no or 1/0 for available/unavaible of that particular room during that particular period?

摘要: 可以说13号房间是从2011年5月15日Dt到2011年12月17日Dt预订的.

In Summary: Lets say room no 13 is booked from Dt-15-06-2011 at 3 to Dt-17-06-2011 at 12 .

同样是从Dt-17-06-2011的16点预订到Dt-18-06-2011的3点预订. 这意味着房间13的可用时间为Dt-17-06-2011的13到Dt-17-06-2011的15的时间.现在我的问题是，通过手动检查非常清楚房间13的可用性. mysql对其进行编程检查.

Again it is booked from Dt-17-06-2011 at 16 to Dt-18-06-2011 at 3 . That means room number 13 is available for the duration Dt-17-06-2011 at 13 to Dt-17-06-2011 at 15 .Now my question is avalability of room 13 is very clear from manual check.But what it is the mysql for it to check programaticaly.

推荐答案

SELECT * FROM hotel_room_book a , hotel_room_book b WHERE a.checkout_date<= 
 $new_checkin_date  AND b.checkin_date >= $new_checkout_date AND a.hotel_id = 
 b.hotel_id AND a.hotel_room_id = b.hotel_room_id ;

这仅在将checkin_date和checkout_date设置为TIMESTAMP(而不是日期)时才有效，因为分别检查日期和时间将涉及复杂的计算. (希望您理解将日期和时间存储在单独的列中的问题)

And this will work only if you have checkin_date and checkout_date as TIMESTAMP instead of date, as checking date and time separately will involve complex computation. (Hope you understand the problem in storing date and time in separate columns)

这篇关于mysql检查特定日期和持续时间的酒店房间可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！