Problem with Active Record - Like and or_like

[eluser]Unknown[/eluser]
Have a weird problem that I may just be over thinking.

I have a simple SQL statement, but for some reason... it ignores the the where clauses and just performs the LIKE portion when the query is ran like this:

SELECT *
FROM (`homes`)
WHERE `assigned_to` = '2451'
AND `status` = '4'
AND `id` LIKE '%gary%'
OR `first_name` LIKE '%gary%'
OR `last_name` LIKE '%gary%'
OR `email_address` LIKE '%gary%'
OR `company` LIKE '%gary%'
OR `address` LIKE '%gary%'
OR `note_promotion` LIKE '%gary%'
OR `note_experience` LIKE '%gary%'
ORDER BY `id` DESC
LIMIT 25

So, it runs all homes, all assignments etc and just works the LIKE portion.

But, when I omit the LIKE statements, it runs the WHERE clauses just fine.

Here is how my like statement is setup:

$this->db->like('id', $search)
->or_like('first_name', $search)
->or_like('last_name', $search)
->or_like('email_address', $search)
->or_like('company', $search)
->or_like('address', $search)
->or_like('note_promotion', $search)
->or_like('note_experience', $search);

[eluser]ivantcholakov[/eluser]
I suppose your initial query is to be modified this way:

Code:

SELECT *
FROM `homes`
WHERE
    `assigned_to` = ‘2451’
    AND `status` =  ‘4’
    AND
    (
     `id`  LIKE ‘%gary%’
     OR `first_name`  LIKE ‘%gary%’
     OR `last_name`  LIKE ‘%gary%’
     OR `email_address`  LIKE ‘%gary%’
     OR `company`  LIKE ‘%gary%’
     OR `address`  LIKE ‘%gary%’
     OR `note_promotion`  LIKE ‘%gary%’
     OR `note_experience`  LIKE ‘%gary%’
    )
ORDER BY `id` DESC
LIMIT 25

On CI2.x this is the possible implementation:

Code:

// These parameters may be assigned by user input.
$assigned_to = 2451;
$status = 4;
$like = 'gary';

$like_escaped = $this->db->escape_like_str($like);

$homes = $this->db->query("
SELECT *
FROM `homes`
WHERE
    `assigned_to` = ".$this->db->escape($assigned_to)."
    AND `status` =  ".$this->db->escape($status)."
    AND
    (
     `id`  LIKE ‘%$like_escaped%’
     OR `first_name`  LIKE ‘%$like_escaped%’
     OR `last_name`  LIKE ‘%$like_escaped%’
     OR `email_address`  LIKE ‘%$like_escaped%’
     OR `company`  LIKE ‘%$like_escaped%’
     OR `address`  LIKE ‘%$like_escaped%’
     OR `note_promotion`  LIKE ‘%$like_escaped%’
     OR `note_experience`  LIKE ‘%$like_escaped%’
    )
ORDER BY `id` DESC
LIMIT 25
")
    ->result(); // Or ->result_array();

On CI3.0-dev, the query biulder can do the job for this case, code is easier to be read:

Code:

// These parameters may be assigned by user input.
$assigned_to = 2451;
$status = 4;
$like = 'gary';

$homes = $this->db
    ->select()
    ->from('homes')
    ->where('assigned_to', $assigned_to)
    ->where('status', $status)
    ->group_start()
        ->like('first_name', $like)
        ->or_like('first_name', $like)
        ->or_like('last_name', $like)
        ->or_like('email_address', $like)
        ->or_like('company', $like)
        ->or_like('address', $like)
        ->or_like('note_promotion', $like)
        ->or_like('note_experience', $like)
    ->group_end()
    ->order_by('id', 'desc')
    ->limit(25)
    ->get()
    ->result(); // Or ->result_array();