need to check room availability

Question

i passed checkin_date , checkout_date using ajax to controller. i need to check what rooms free between checkin_date AND checkout_date... what is the wrong of that query?

public function available_rooms($checkin_date,$checkout_date ){
       $arooms = DB::SELECT("SELECT * FROM tbl_rooms WHERE room_number NOT IN (SELECT room_number FROM bookings WHERE ('$checkin_date' BETWEEN checkin_date AND checkout_date) AND ('$checkout_date' BETWEEN checkin_date AND checkout_date) )");

Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) — Yogi, Jun 21 '22 at 05:16
no. i need to check to both $checkin_date and $checkout_date. using AND clause. can you see what is the problem of my query,s logic — nadeesha, Jun 21 '22 at 05:33
Your query logic is incorrect. If queried period contains existing row's period completely then the overlapping won't be detected. — Akina, Jun 21 '22 at 06:13

score 0 · Answer 1 · answered Jun 21 '22 at 06:17

Use the ranges overlapping detection (ranges overlaps when each range start is less than opposite range end).
Use WHERE NOT EXISTS, it breaks the searching for a row when first matched occurence found rather than WHERE NOT IN which gathers all matched rows.

SELECT *
FROM room
WHERE NOT EXISTS ( SELECT NULL
                   FROM booking
                   WHERE room.room_id = booking.room_id
                     AND booking.checkin_date < $checkout_date
                     AND $checkin_date < booking.checkout_date );

Depends on needed logic you may use not strict but soft (>= and <=) unequiation operators.

The index booking (room_id, checkin_date, checkout_date) should improve the query.

need to check room availability

1 Answers1