combining mysql AND OR queries in Codeigniter

Question

I want to combine AND OR mysql queries in CI. I have already seen this thread: http://codeigniter.com/forums/viewthread/92818/. But they don't provide the exact solution there.

How do I create the following query using strictly the CI framework? (I can create the query easily without the brackets but then it is not the same query.)

SELECT * FROM `Persons` WHERE
LastName='Svendson' AND Age="12" AND
(FirstName='Tove' OR FirstName='Ola' OR Gender="M" OR Country="India") 

P.S.: This is just a sample query even if it makes no sense & Do not suggest writing the entire OR part of the query inside a single where().

EDIT: Basically I want the implementation of the following simple query:

SELECT * FROM `table` WHERE field1='value1' AND (field2='value2' OR field3='value3') 

Answer 1

In CodeIgniter 3 there are new methods group_start() and group_end() which serve exactly for this purpose.

return $this->db
     ->where('LastName', 'Svendson');
     ->where('Age', 12);
     ->group_start()
         ->where('FirstName','Tove')
         ->or_where('FirstName','Ola')
         ->or_where('Gender','M')
         ->or_where('Country','India')
     ->group_end()
     ->get('Persons')
     ->result();

Answer 2

and this will work?

$this->db->where('LastName', 'Svendson');
$this->db->where('Age', 12);
$this->db->where("(FirstName='Tove' OR FirstName='Ola' OR Gender='M' OR Country='India')", NULL, FALSE);
$query = $this->db->get('Persons');
return $query->result();

Answer 3

using codeigniter 3.0 frame work,there is new feature available for separate or where and where operation.that is,group by and group end

code like,

$this->db->where('LastName', 'Svendson');
$this->db->where('Age', 12);
$this->db->group_start();
$this->db->or_where('FirstName','Tove');
$this->db->or_where('FirstName','Ola');
$this->db->or_where('Gender','M');
$this->db->or_where('Country','India');
$this->db->group_end();
$query = $this->db->get('Persons');
return $query->result();

Answer 4

In Codeigniter we can use like this it easy to understand.

$sql = "SELECT
            *
        FROM
            `Persons`
        WHERE
            LastName = 'Svendson'
        AND Age = '12'
        AND (
            FirstName = 'Tove'
            OR FirstName = 'Ola'
            OR Gender = 'M'
            OR Country = 'India'
        )";

$query = $this->db->query($sql);

return $query->result();

Answer 5

You can use this simply

$this->db->where("status","live")->or_where("status","dead");

you can also use

$this->db->where("(status='live' OR status='dead')");

Answer 6

Try using Query grouping

Link for it

http://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

Answer 7

Currently with CI2 you can't access the Query Builder method ($this->db->_compile_select() ) of the Database class without extending the Database class and changing the method's access type from private to public/protected, that kills the ability to build Subquery's like your trying to build using the ActiveRecord class. The only method to make a subquery like the one your trying to build would be to just use the db query method

$table = $this->db->dbprefix('tablename');

$sql = "SELECT * FROM `{$table}` WHERE field1='?' AND (field2='?' OR field3='?') ";
$this->db->query($sql,array($field1,$field2,$field3));  

There was a blog post about doing this at CI Subquerys but it's out of date and only works on CI 1.7 Hope that helps a bit.

Answer 8

I needed this for CodeIgniter 2, but I needed the values still escaped so csotelo's answer wasn't good for me and I didn't want to rewrite the whole query like Shawn C's answer, so I ended up doing this:

$this->db->where('LastName', $lastName);
$this->db->where('Age', $age, false);
$this->db->where('1 AND ( 0', null, false); // This one starts the group
  $this->db->or_where('FirstName', $firstName1);
  $this->db->or_where('FirstName', $firstName2);
  $this->db->or_where('Gender', $gender);
  $this->db->or_where('Country', $country);
$this->db->or_where('0 )', null, false); // This one ends the group
$query = $this->db->get('Persons');

Which generates the following query:

SELECT *
FROM (`Persons`)
WHERE `LastName` =  'Svendson'
AND Age = 12
AND 1 AND ( 0 -- This one starts the group
OR `FirstName` =  'Tove'
OR `FirstName` =  'Ola'
OR `Gender` =  'M'
OR `Country` =  'India'
OR 0 ) -- This one ends the group

Which would be the same as this:

SELECT * FROM (`Persons`) WHERE
`LastName` = 'Svendson' AND Age = 12 AND 1 AND
(0 OR `FirstName` = 'Tove' OR `FirstName` = 'Ola' OR `Gender` = 'M' OR `Country` = 'India' OR 0)

Answer 9

The query itself doesn't make sense, you're selecting:

• Tove Svendson, age 12
• Ola Svendsen, age 12
• any male named Svendson, age 12
• any person from India named Svendson, age 12

Tove seems like a man's name, so selecting the gender is unnecessary. Ola seems like a girl's name, so selecting the gender is not only unnecessary, but it just doesn't make sense. Your query will return any 12 year old male named Svendson, any 12 year old from India named Svenson, and Tove and Ola Svendson, IF they're 12 years old.

Why don't you want to put it between () brackets? Do you want to accomplish it with active record for some reason?