CodeIgniter添加if条件后MySQL查询出现1064语法错误求助

在CodeIgniter代码中添加客户身份判断的if条件语句后，执行MySQL查询时出现语法错误。

相关代码 #

if($this->ion_auth->is_customer())
      $this->db->where('company_database.cdb_customer_id',$this->session->userdata('user_id'));

$this->db->select('company.*, cities.name as company_city, states.name as company_state, countries.name as company_country');
$this->db->from('company as company');
$this->db->join(CITIES.' as cities','cities.id = company.company_city_id' ,'left');
$this->db->join(STATES.' as states','states.id = company.company_state_id' ,'left');
$this->db->join(COUNTRIES.' as countries','countries.id = company.company_country_id' ,'left');
$this->db->join(COMPANY_DATABASE.' as company_database','company_database.cdb_company_id = company.company_id' ,'left');

if($this->ion_auth->is_customer())
  $this->db->where('company_database.cdb_customer_id',$this->session->userdata('user_id'));

$this->db->where('company.company_delete_status',NOT_DELETED);    

$query = $this->db->get();

echo '<pre>';
echo $this->db->get_compiled_query();
print_r($query->result());
echo $this->db->last_query();

报错信息 #

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `company_database`.`cdb_customer_id` = '19' AND `company`.`company_delete_' at line 2

SELECT * WHERE `company_database`.`cdb_customer_id` = '19' AND `company`.`company_delete_status` = 0

从报错的SQL语句能直接看出问题：生成的SQL缺少FROM子句和表关联部分，直接写成了SELECT * WHERE ...，这完全不符合MySQL语法规范。

问题根源是调用$this->db->get_compiled_query()的时机错误——这个方法会重置当前查询构建器的状态，导致后续执行$this->db->get()时，之前设置的from、join等配置全部丢失，只能生成残缺的SQL。

解决方法有两种：

• 改用$this->db->last_query()获取已执行的完整SQL，把它放到$query = $this->db->get();之后：

  $query = $this->db->get();
  
  echo '<pre>';
  echo $this->db->last_query();
  print_r($query->result());
  

• 如果一定要用get_compiled_query()，提前获取并保存结果，避免影响后续查询执行：

  // 先获取编译好的SQL
  $compiled_sql = $this->db->get_compiled_query();
  // 执行查询
  $query = $this->db->get();
  
  echo '<pre>';
  echo $compiled_sql;
  print_r($query->result());
  echo $this->db->last_query();
  

内容的提问来源于stack exchange，提问作者Zivaan Solutions