issue in mysql query in codeigniter only if I add if condition-CodePudding

In below code, whenever I am adding below code with if conditions, i am getting error

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

What is the issue above query ?

I am getting below issue related to 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

Filename: controllers/Test.php

Line Number: 112

CodePudding user response：

You don't have a "from" clause in your where clause.

select * {from company} where 'company_database'.'cdb_customer_id' = ....

I suspect that the function

$this->ion_auth->is_customer()

may be calling another DB query and that pretty much completes the query you started above and once completed it does the $this->db with just the where clauses after.

To fix call the $this->ion_auth->is_customer() before you do $this->db->select and then in the IF statement simply just use the boolean returned so you don't make another call to a query while you form another query.

Example:

--ADD THIS LINE
$bIsClient = $this->ion_auth->is_customer();

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

    --AND CHANGE THIS 
    if($bIsClient)
      $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();