用户如何在日期范围内搜索现有房间？

Question

我想创建一个搜索页面，该搜索页面含有一个房间名称，签到和退房日期，以检查是否有房间。

因此，如果用户想搜索预订房间，则必须输入房间名称，入住日期和退房日期，然后单击搜索按钮。

如果房间有现有的预订，并且日期范围与用户输入重叠，则不会显示出来，其他情况。现在，我的SQL输出仅基于房间名称，我不知道为什么它不检查日期重叠。

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

这是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();

房间表

预订表

对不起，我无法在此处显示图像，所以我附上上面的链接。

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

也许会有所帮助