CodeIgniter $this->db->where(); Group Combine ANDs and ORs

Question

I need to create this select:

SELECT * FROM units
WHERE ((um like '%str%'  AND desc like '%str%') AND deleted = 0)

so i tried using

$search = 'str';

$this->db->from('units');
$this->db->where("(um LIKE '%".$this->db->escape_like_str($search)."%' or
desc LIKE '%".$this->db->escape_like_str($search)."%') and deleted=0");
return $this->db->get();

but that did not work, even though on my other tables, controllers, modules it works fine. edit: I saw why it did not work, because of desc... but still my question goes below...

I tried using this and works properly

$this->db->from('units');
$this->db->where('deleted',0);
$this->db->like('desc', $search);
$this->db->or_like('um', $search);

But this formulates as:

WHERE deleted = 0 AND desc like  '%str%' AND um like  '%str%'

Can I combine or_like and like as 1? and where deleted as another, because now it shows all the deleted because of the or_like.

This looks similar: http://stackoverflow.com/questions/6470267/grouping-where-clauses-in-codeigniter — Sam, Apr 06 '13 at 22:07
By the way - don't use `desc` as a column name - it's a keyword in some DBs - ie. MySQL — dakdad, Apr 07 '13 at 08:18
yeah found that the hard way. anyway I did not change it and used `desc` instead. — Sid, Apr 07 '13 at 08:27

itsme · Answer 1 · 2013-04-07T12:59:10.260

0

for this

SELECT * FROM units WHERE ((um like '%str%' AND desc like '%str%') AND deleted = 0)

you have to do:

$this->db->select('*');
$this->db->from('units');
$this->db->where("((units.um LIKE '%str%'  AND units.desc LIKE '%str%') AND deleted = '0')",NULL);
$query = $this->db->get();
return $query->result();

edited Apr 07 '13 at 12:59

answered Apr 07 '13 at 01:52

itsme

48,972
96
224
345

actually that wont work because you must do `desc` instead of desc. But I am looking to combine like, or_like as a group ( )... May I know if it is not possible? thanks – Sid Apr 07 '13 at 07:59
@Sid can't catch you.. what you mean by **you have to do desc**? group() and like() are different commands , please write down the query you need to do and i can help you! – itsme Apr 07 '13 at 12:58
@Sid anyway you should be able to use **units.desc** instead of **desc** – itsme Apr 07 '13 at 12:59
$this->db->select('*'); $this->db->from('units'); $this->db->where("(um LIKE '%".$this->db->escape_like_str($search)."%' or desc LIKE '%".$this->db->escape_like_str($search)."%') and deleted=0"); return $this->db->get(); – Brij Raj Singh - MSFT Mar 13 '14 at 06:52

CodeIgniter $this->db->where(); Group Combine ANDs and ORs

1 Answers1