Check Available Rooms For Selected Dates

You can check the available rooms with a subquery.

PHP Code:

$this->db
->select('room_no')
->from('reservation')
->where('days >=', $date_in)
->where('days <=', $date_out);
$where_clause = $this->db->get_compiled_select();

$query = $this->db->where("room_id NOT IN ($where_clause)", NULL, FALSE)->get('rooms');
if ($query->num_rows() == 0) {
   return FALSE;
}
else {
   return $query->result();
} 


the complexity of WHERE IN/NOT IN is O(n^2) which is why we should avoid this type of query. JOIN complexity is O(N+M) which should be preferred over WHERE IN/NOT IN clause.


My solution would be:
add to reservation table date_in and date_out columns.
then to get free rooms in specified dates use.

Code:

$this->db
->select('*')
->from('room r')
//join with ones that are not available in the given dates
->join('reservation rs', "r.room_no = rs.room_no and (rs.date_in <= '$date_in' and rs.date_out > '$date_in') and (rs.date_in < '$date_out' and rs.date_out > '$date_out'  ", 'LEFT')
//then filter those unavailable rooms
->where('rs.id', NULL);

It should work