combining mysql AND OR queries in Codeigniter

问题:

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

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

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

回答3:

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

回答4:

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

回答5:

Try using Query grouping

Link for it

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

回答6:

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

回答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.

回答8:

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?