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') 

Heavyarmed

Answer 1

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();

Foreworn

yes this will work & this is what I am currently using. But this is not strictly CI way, I have to escape strings manually. Also I suspected this kind of answer so had mentioned in the question Do not suggest writing the entire OR part of the query inside a single where(). – Heavyarmed

I asked you in my initial answer if you had some reason to accomplish this with active record, since defining a query within () IS 'strictly codeigniter', it just isn't active record. Anyway, this is not possible within CI as they won't allow you to do nested queries in AR. There is a query string builder called IgnitedQuery for that: assembla.com/wiki/show/IgnitedRecord/IgnitedQuery. – Subcutaneous

This doesn't work for me. The 2nd where condition is overriding the 1st where condition. CodeIgniter V 2.1.4 – Besiege

What is the ...NULL, FALSE) for? – Odey

NULL is the Value parameter (no obligatory), FALSE is remove query quotes (FALSE because this affect parenthesis). – Foreworn

Look at the @gadelat's response for a new functionality on CI3. – Forge

@Heavyarmed Did you fix the above issue. I am also facing same issue. My Query is as: $this->db->where('active', 'Y'); $this->db->or_like($where_clause); where as $where_clause is an array for SEARCH using OR. I need to get all active records matching search criteria. – Pace

Answer 2

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();

Decimalize

When was this version released?> – Pace

This is the way to do it now. +1 – Limitless

correct me if i'm wrong please. does it need to have a semi colon in your statement except for the last one? sorry new to CI, – Durand

It doesn't. Changed the post to show how to do it fully fluent – Decimalize

This solution made my day!!! Took my quite a while to figure out this simple solution. Thank you. – Stronghold

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();

Phyllome

Great! Will try this out too sometime – Heavyarmed

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();

Daffi

to me, it seems more easier than in-built functions – Elegit

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')");

Helvellyn

Answer 6

Try using Query grouping

Link for it

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

Mealtime

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.

Brigandine

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)

Card

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?

Subcutaneous

The query is just a test one. I randomly wrote whatever came to my mind. Basically I need f1=v1 AND (f2=v2 OR f3=v3) kind of queries – Heavyarmed

& also my question was nothing about selecting, so u needn't analyze my query – Heavyarmed

Dude, easy on the downvote man, just trying to get things clear. Anyway, have you tried $this->db->from("Persons"); $this->db->where("LastName","Svendson"); $this->db->where("age","12"); $this->db->or_where_in('FirstName',$arrFirstnames); $this->db->or_where_in('gender','M'); $this->db->or_where_in('Country','India')[/code] – Subcutaneous

The OR part wont come within brackets. What you are generating is the query without brackets. I had clearly mentioned in my question that (I can create the query easily without the brackets but then it is not the same query.) – Heavyarmed