Several similar columns in a query
I am using CodeIgniter 2.1.
I have a table with first and last names as well as other columns. I want to get those clients who have a name,firstname like '%q%' OR lastname like '%q' AND Status = 1
I am using the following set of active records:
$this->db->select("$this->table.*, $this->table.Active as 'Status'");
$like = array(
"$this->table.FirstName" => $where['customer_name'],
"$this->table.LastName" => $where['customer_name']
);
$this->db->or_like($like);
Generated request:
SELECT 'customers'.*, 'customers'.'Active' as 'Status'
WHERE 'customers'.'Active' = '1' AND
'customers'.'FirstName' LIKE '%michael%' OR
'customers'.'LastName' LIKE '%michael%'
ORDER BY 'customers'.'RegDate' desc LIMIT 10
The above query will return those customers whose first name is "michael" either by first name or last name AND active = 1, it will return all records that have michael in them, either active or not.
I need records with the name "micheal" in the first or last name AND active = 1, I know in the custom query, if I add a Like clause between the little brace '()' it will give me the desired result. I want the above request to be parsed as:
SELECT 'customers'.*, 'customers'.'Active' as 'Status'
WHERE 'customers'.'Active' = '1' AND
('customers'.'FirstName' LIKE '%michael%' OR
'customers'.'LastName' LIKE '%michael%')
ORDER BY 'customers'.'RegDate' desc LIMIT 10
I think you also need to use the or_where function
Grouping is not suitable when placing where and or_like with multiple columns in codeigniter. I faced this problem too,
The best solution is to write the query in string format and then execute it.
//You can used like that
$this->db->where("(`car_model` LIKE '%$search_keyword_new%' OR `car_make_model` LIKE '%$search_keyword_new%' OR `car_name` LIKE '%$search_keyword_new%')");
$this->db->where('user_id',$user_id);