Nested where clauses codeigniter mysql query

Question

Is there any way to get nested where clauses? e.g.:

SELECT * FROM table WHERE (colA = 'valueA' AND colB = 'valueB') OR (colA = 'valueC' AND colB = 'valueD')

I know I could just write this into a query function call e.g.:

$this->db->query("SELECT ...")

But I was wondering if there was a "proper" way to do it in codeigniter e.g.:

$this->db->where(array('colA'=>'valueA'), array('colB'=>valueB'))->or_where(array('colA'=>'valueC'), array('colB'=>'valueD'))

thanks

complex857 · Accepted Answer · 2015-03-31 20:31:31Z

7

With codeigniter 3, now there is, see the update!
~~There's no where() method usage variant with arrays that would allow you to do that.~~ In these situations i usually just build the part in one long string like this:

$this->db->where("
    (
    (colA = '".$this->db->escape($v0)."' and colB = '".$this->db->escape($v1)."') 
    or 
    (colA = '".$this->db->escape($v2)."' and colB = '".$this->db->escape($v3)."') 
    )
");

Escaping can be done with escape(does some autodetection) or escape_str or escape_like_str manually depending on what the parameter expected to be or what the predicate in use.

If i'm on a project that uses the Datamapper library, i prefer to use the group_start() and group_end() methods when building these kind of queries, they have a lot of different flavor of these.

Update

Now with, Codeigniter 3 which have grouping methods in the query builder, so you can do ->group_start()s and ->group_end()s.

edited Mar 31, 2015 at 20:31

answered Aug 13, 2012 at 19:14

complex857

20.9k6 gold badges54 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Clayson Over a year ago

Ah, thanks for that! :) That's a bit better than doing the $this->db->query("...") version. Thank you.

GautamD31 · Accepted Answer · 2012-08-21 09:40:34Z

-1

You can also try like

$this->db->where(condition1);
$this->db->or_where(condition2);

answered Aug 21, 2012 at 9:40

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

Collectives™ on Stack Overflow

Nested where clauses codeigniter mysql query

2 Answers 2

Update

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related