我正在研究酒店预订系统，目前我正在尝试选择可用房间(未预订)。

Rooms DB Structure:
ID
ROOM NAME
CAPACITY

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

这是我当前的代码:

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

一个用户可以一次预订多个房间，预订的房间 ID 存储在“ROOMS”列中，用逗号分隔，例如:2, 3, 5

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

问题在这里:$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 (1, 2) ");它可以完美地工作，但不适用于第二个查询的上层方法。

对不起我的英语...

非常感谢所有能提供帮助的人!

最佳答案

终于在您的帮助下我解决了我的问题!

工作代码:

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

非常感谢!

关于php - Codeigniter - select where id not in (另一个查询结果)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37981357/