CODEWITHSUNDEEP
phpsqlcodeigniteractiverecordlogical-grouping
Geril
Geril

Reputation: 159

Codeigniter active record query with a WHERE clause containing a mix of LIKE and non-LIKE conditions

I want to select products from my table where Akcie is 1 and Stav is also 1 and satisfies one of the LIKE conditions.

However my attempt is returning rows where Akcia is 0. Why? Here is my code:

$q = $this->db->where('Akcia', 1)
      ->like('Titul', $vyraz)
      ->or_like('PodTitul', $vyraz)
      ->or_like('Autor1', $vyraz)
      ->or_like('Autor2', $vyraz)
      ->or_like('Autor3', $vyraz)
      ->or_like('Autor4', $vyraz)
      ->or_like('Prekladatel', $vyraz)
      ->or_like('Rozmer', $vyraz)
      ->or_like('Vydavatelstvo', $vyraz)
      ->or_like('ISBN', $vyraz)
      ->or_like('EAN', $vyraz)
      ->or_like('Popis', $vyraz)
      ->or_like('KratkyPopis', $vyraz)
      ->or_like('RokVydania', $vyraz)
      ->where('stav', 1)
      ->order_by('id', 'desc')
      ->limit($limit)
      ->offset($offset)
      ->get('knihy');
     
return $q->result();

Upvotes: 0

Views: 624

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

Your coding attempt is missing the required parenthetical grouping to encapsulate the OR logic.

Also, because you are looking for matches in a number of columns using the same variable/value, you can D.R.Y. your code using an iterating function or loop.

$columns = ['Titul', 'PodTitul', 'Autor1', 'Autor2', 'Autor3',
            'Autor4', 'Prekladatel', 'Rozmer', 'Vydavatelstvo',
            'ISBN', 'EAN', 'Popis', 'KratkyPopis', 'okVydania'];

$this->db->group_start();
array_walk(
    $columns,
    fn($field) => $this->db->or_like($field, $vyraz)
);
$this->db->group_end();

return $this->db
    ->order_by('id', 'desc')
    ->get_where('knihy', ['Akcia' => 1, 'stav' => 1], $limit, $offset)
    ->result();

If $vyraz = 'term', then the rendered query will resemble this:

SELECT *
FROM `knihy`
WHERE (
    `Titul` LIKE '%term%' ESCAPE '!'
    OR  `PodTitul` LIKE '%term%' ESCAPE '!'
    OR  `Autor1` LIKE '%term%' ESCAPE '!'
    OR  `Autor2` LIKE '%term%' ESCAPE '!'
    OR  `Autor3` LIKE '%term%' ESCAPE '!'
    OR  `Autor4` LIKE '%term%' ESCAPE '!'
    OR  `Prekladatel` LIKE '%term%' ESCAPE '!'
    OR  `Rozmer` LIKE '%term%' ESCAPE '!'
    OR  `Vydavatelstvo` LIKE '%term%' ESCAPE '!'
    OR  `ISBN` LIKE '%term%' ESCAPE '!'
    OR  `EAN` LIKE '%term%' ESCAPE '!'
    OR  `Popis` LIKE '%term%' ESCAPE '!'
    OR  `KratkyPopis` LIKE '%term%' ESCAPE '!'
    OR  `okVydania` LIKE '%term%' ESCAPE '!'
 )
AND `Akcia` = 1
AND `stav` = 1
ORDER BY `id` DESC

Upvotes: 0

Vhinz
Vhinz

Reputation: 11

Well if I am correct the code will produce this condition on the SQL :

WHERE Akcia = 1 AND Titul LIKE 'param' OR PodTitul LIKE 'param' etc.

so if any of the "or_like" param get a hit, automatically your condition for the Akcia will be disregarded and the system will still show all records whether they have 1 or 0 value

what you need is to include a parenthesis like this:

WHERE Akcia = 1 AND (Titul LIKE 'param' OR PodTitul LIKE 'param' etc.)

I think this is what you need, refer to this link :

http://codeigniter.com/forums/viewthread/105664/#548079

Upvotes: 1

Related Questions