Check two date was not between two another date + MYSQl

Question

I am writhing a query for a hotel reservation and booking system but I got a problem with selecting rooms was not reserved between two date . my query is alike this but it's not working as well and query return noting but actually I have 3 room with room type 1 . Sql fiddle : http://sqlfiddle.com/#!2/b97419

SELECT tbl_room.room_no, tbl_room.type_id, tbl_Reservation.room_id
FROM tbl_room
LEFT JOIN tbl_Reservation ON tbl_room.id = tbl_Reservation.room_id
WHERE (
(
tbl_Reservation.checkin_data < '" . $checkin . "'
AND tbl_Reservation.checkout_data < '" . $checkin . "'
)
OR (
tbl_Reservation.checkin_data > '" . $checkout . "'
AND tbl_Reservation.checkout_data > '" . $checkout . "'
)
)
AND tbl_room.type_id =1

thanks for your helping and sorry for my bad english .

your first part of where contains $checkin twice and not contains the $checkout for checkout_data — vaso123
There are no data in tb_room. Anyway, did you read what i wrote??? You need to change $checkin to $checkout at the first part of your WHERE because you are using $checkin where you need to use $checkout — vaso123

user2736880 user2736880 · Accepted Answer · 2014-11-11T13:57:45

Try something like this

SELECT 
    tbl_room.room_no, 
    tbl_room.type_id, 
    tbl_room.id,

    tr.id as reserv_status

FROM tbl_room

 LEFT JOIN tbl_Reservation tr ON tbl_room.id = tr.room_id AND 
           (
             (tr.checkin_data <= "'$checkin'" AND tr.checkout_data >= "'$checkin'")
             OR
             (tr.checkin_data <= "'$checkout'" AND tr.checkout_data >= "'$checkout'")
           )

 WHERE 

 tbl_room.type_id =1  AND tr.id IS NULL

Check two date was not between two another date + MYSQl

1 Answers