Idea13 Idea13 - 5 months ago 39
PHP Question

Codeigniter - select where id not in (another query result)

I'm working into a hotel booking system, currently I'm trying to select available rooms (not reserved).

Rooms DB Structure:
ID
ROOM NAME
CAPACITY

HOTEL RESERVATIONS DB STRUCTURE:
ID
CHECK_IN
CHECK_OUT
ROOMS
...


This is my current code:

function searchFreeRooms($data){
$check_in = $data['fields']['check_in'];
$check_out = $data['fields']['check_out'];
$this->db->select("*");
$this->db->from('core_hotel_rooms');
$this->db->where("id NOT IN (select rooms,total_guests from res_hotel where check_in <= '$check_in' AND check_out >= '$check_in' OR check_in <= '$check_out' AND check_out >= '$check_out' OR check_in >= '$check_in' AND check_out <= '$check_out' ) ");
$query = $this->db->get();
return $query->result();
}


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

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.

Problem is here:
$this->db->where("id NOT IN (select rooms,total_guests from res_hotel where check_in <= '$check_in' AND check_out >= '$check_in' OR check_in <= '$check_out' AND check_out >= '$check_out' OR check_in >= '$check_in' AND check_out <= '$check_out' ) ");


I tried this:
$this->db->where("id NOT IN (1, 2) ");
and it works perfectly but not upper method with second query.

Sorry for my english...

Many thanks to all those who can help!

Answer

Here is the solution to your problem. Hope this help:

function searchFreeRooms($data){
  $check_in = $data['fields']['check_in'];
  $check_out = $data['fields']['check_out'];

  $query1 = $this->db->query("select rooms from res_hotel where check_in <= '".$check_in."' AND check_out >= '".$check_in."' OR check_in <= '".$check_out."' AND check_out >= '".$check_out."' OR check_in >= '".$check_in."' AND check_out <= '".$check_out."'")->result_array();
  $room_id= array();
  foreach($query1 as $row){
     $room_id[] = $row->rooms;
   }
  $room = implode(",",$room_id);

  $query = $this->db->query("select * from rooms where id not in (".$room.")");

      return $query->result();
     }
Comments