title: Codeigniter - select where id not in (another query result)
body: <p>I'm working into a hotel booking system, currently I'm trying to select available rooms (not reserved).</p>
  
  <pre><code>Rooms DB Structure:
  ID
  ROOM NAME
  CAPACITY
  
  HOTEL RESERVATIONS DB STRUCTURE:
  ID
  CHECK_IN
  CHECK_OUT
  ROOMS
  ...
  </code></pre>
  
  <p>This is my current code:</p>
  
  <pre><code>function searchFreeRooms($data){
    $check_in = $data['fields']['check_in'];
    $check_out = $data['fields']['check_out'];
    $this-&gt;db-&gt;select("*");
    $this-&gt;db-&gt;from('core_hotel_rooms');
    $this-&gt;db-&gt;where("id NOT IN (select rooms,total_guests from res_hotel where check_in &lt;= '$check_in' AND check_out &gt;= '$check_in' OR check_in &lt;= '$check_out' AND check_out &gt;= '$check_out' OR check_in &gt;= '$check_in' AND check_out &lt;= '$check_out' ) ");
    $query = $this-&gt;db-&gt;get();
    return $query-&gt;result();
   }
  </code></pre>
  
  <p>A user can book many rooms in one time, and reserved room id's are stored in column "ROOMS" seperated with commas ex: 2, 3, 5</p>
  
  <p>In my front, room that exist in this column should not be displayed but I'm having trouble because only first id(room) before comma is selected, example: 2,3,5 > only 2 is selected and 3,5 still are displayed in my front.</p>
  
  <p>Problem is here: <code>$this-&gt;db-&gt;where("id NOT IN (select rooms,total_guests from res_hotel where check_in &lt;= '$check_in' AND check_out &gt;= '$check_in' OR check_in &lt;= '$check_out' AND check_out &gt;= '$check_out' OR check_in &gt;= '$check_in' AND check_out &lt;= '$check_out' ) ");</code></p>
  
  <p>I tried this: <code>$this-&gt;db-&gt;where("id NOT IN (1, 2) ");</code> and it works perfectly but not upper method with second query.</p>
  
  <p>Sorry for my english...</p>
  
  <p>Many thanks to all those who can help!</p>