phpcodeigniteractiverecordsql-likelogical-grouping

How to use like, or_like and get_where together in Codeigniter 3


I'm trying to search with a keyword but only in the rows where business_type is manufacturer but it's not working and it's getting all the rows. This is the method in my model:

public function search($keyword) {
    $this->db->like('category',$keyword);
    $this->db->or_like('keyword',$keyword);
    $this->db->or_like('products_deals_with',$keyword);
    $this->db->or_like('buisness_name',$keyword);
    $params['conditions'] = array(
        'business_type' => 'manufacturer'
    );
    $query = $this->db->get_where('business_listings', $params['conditions']);
    return $query->result_array();
}

Generated query is:

SELECT * FROM `business_listings`
WHERE `category` LIKE '%%' ESCAPE '!'
OR `keyword` LIKE '%%' ESCAPE '!'
OR `products_deals_with`LIKE '%%' ESCAPE '!'
OR `buisness_name` LIKE '%%' ESCAPE '!'
AND `business_type` = 'manufacturer'

Solution

  • I've found the solution. I've to use $this->db->group_start(); and $this->db->group_end();

    public function search($keyword) {
    
        $this->db->select('*');
        $this->db->where("business_type = 'manufacturer'");
        $this->db->group_start();
        $this->db->like('category',$keyword);
        $this->db->or_like('keyword',$keyword);
        $this->db->or_like('products_deals_with',$keyword);
        $this->db->or_like('buisness_name',$keyword);
        $this->db->group_end();
        $query = $this->db->get('business_listings');
        // echo $this->db->last_query();
        return $query->result_array();
    
    }
    

    Generated Query:

    SELECT * FROM `business_listings`
    WHERE `business_type` = 'manufacturer'
    AND (
    `category` LIKE '%%' ESCAPE '!'
    OR `keyword` LIKE '%%' ESCAPE '!'
    OR `products_deals_with` LIKE '%%' ESCAPE '!'
    OR `buisness_name` LIKE '%%' ESCAPE '!' )