mysql酒店入住日期选择的语句,mysql检查特定日期和持续时间的酒店房间可用性...

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.

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 ;

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

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

I am searching for

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:

case1:(1,2,3 in above)

Room is available

if required checkin_date and checkin_time >= booked checkout_date and checkout_time

and required checkout_date and checkout_time <= booked checkin_date and checkint_time

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

Room is available

if required checkin_date and checkin_time <= booked checkin_date and checkin_time

and required checkout_date and checkout_time <= booked checkin_date and checkint_time

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?

In Summary:

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

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 ;

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)