I'm using CodeIgniter 2.1.
I have a table which have firstname and lastname and other columns
I want to get those customers which have firstname like '%q%' OR lastname like '%q' AND Status = 1
I'm using the following Active Record set:
$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);
The generated query is:
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
Above query brings those customers which have 'michael' either in first name or last name AND active = 1, it brings all the records which have michael either they are active or not.
I need those record which have 'micheal' in firstname or lastname AND active = 1, I know in custom query, if I place Like clause between small brace ' ()', it will give me my desired result.
I want the above query to be parse 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
Best Answer
I think you need to also make use of the or_where function