How can user search existing room between an date range?

Question

I want to create a search page that takes a room name, check-in, and check-out date to check if a room is available.

So if users want to search rooms for booking they must input room name, check-in date, and check-out date then click the search button.

If a room has an existing booking and the date range overlaps with the user input then it will not show, and otherwise. Now my SQL output is based on the room name only, I have no idea why it does not check for date overlaps.

   function search($room, $start, $end) {

      $this->db->select('  rId as id,
                           rName as name,
                           rCapacity as capacity,
                           bCheckin as checkin,
                           bCheckout as checkout,
                           bBookingTime as bookingtime
                        ');

      $this->db->from('room');
      $this->db->join('booking', 'rId = brId', 'left');
      $this->db->like('rName', $room);     
      $this->db->where('bCheckout' > $start AND 'bCheckin' < $end);
      $this->db->where('brId', NULL);
      
      return $this->db->get()->result_array();
   }

This the raw SQL:

$query = " SELECT rId as id,
                           rName as name,
                           rCapacity as capacity,
                           bCheckin as checkin,
                           bCheckout as checkout,
                           bBookingTime as bookingtime
      FROM room r
      LEFT
      JOIN booking b
         ON b.brId= r.rId
      AND NOT ( $end >= b.bCheckout OR $start<= b.bCheckin )
      WHERE r.rName = $room
      WHERE b.rbId IS NULL";
      return $this->db->query($query)->result_array();

room Table

booking Table

Sorry I can't show image here so I attach in above link.

Answer 1

$query = " SELECT r.Id as id,
                           r.Name as name,
                           r.Capacity as capacity,
                           b.Checkin as checkin,
                           b.Checkout as checkout,
                           b.BookingTime as bookingtime
      FROM room r
      LEFT
      JOIN booking b
         ON b.brId= r.rId
      WHERE
          NOT($end >= b.bCheckout OR $start<= b.bCheckout)
      AND r.rName LIKE $room
      AND b.orderRuangan IS NULL";

Maybe it will helpful