php Codeigniter - 选择 id 不在的地方（另一个查询结果）

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

提问by Idea13

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

一个用户可以一次预订多个房间，预订的房间 id 存储在“ROOMS”列中，以逗号分隔，例如：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.

在我的前面，不应显示此列中存在的房间，但我遇到了麻烦，因为只选择了逗号之前的第一个 id(room)，例如：2,3,5 > 仅选择了 2 而 3,5 仍然是显示在我的面前。

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

问题在这里： $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.

我试过这个：$this->db->where("id NOT IN (1, 2) ");它工作得很好，但不是第二个查询的上层方法。

Sorry for my english...

对不起我的英语不好...

Many thanks to all those who can help!

非常感谢所有可以提供帮助的人！

回答by Idea13

Finally with all your help I've fixed my problem!

终于在你的帮助下我解决了我的问题！

Working code:

工作代码：

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' )");
  $query1_result = $query1->result();
  $room_id= array();
  foreach($query1_result as $row){
     $room_id[] = $row->rooms;
   }
  $room = implode(",",$room_id);
  $ids = explode(",", $room);
  $this->db->select("*");
  $this->db->from('core_hotel_rooms');
  $this->db->where_not_in('id', $ids);
  $query = $this->db->get();
  return $query->result();
 }

Thanks a lot!

非常感谢！

回答by Niklesh Raut

Consider using brackets

考虑使用括号

   $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' ) ) ");

回答by Robbin Lama

$result = $this->db->select('room')
     ->get('your_table');

    foreach($result as $item) {
        $array[] = $item['id'];         
    }
    $a = implode(',', $array);

$this->where_not_in('id', $a);

do something like this.

做这样的事情。

回答by mrdragon

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