php 在 Codeigniter 中结合 mysql AND OR 查询

combining mysql AND OR queries in Codeigniter

提问by gopi1410

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.

我想在 CI 中结合 AND OR mysql 查询。我已经看过这个帖子：http: //codeigniter.com/forums/viewthread/92818/。但是他们没有在那里提供确切的解决方案。

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

如何严格使用 CI 框架创建以下查询？（我可以在没有括号的情况下轻松创建查询，但它不是同一个查询。）

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

PS：这只是一个示例查询，即使它毫无意义也不建议将查询的整个 OR 部分写入单个where().

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

编辑：基本上我想要实现以下简单查询：

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

回答by csotelo

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

回答by gadelat

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

在 CodeIgniter 3 中，有新方法group_start() 和 group_end()正好用于此目的。

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

回答by Andy

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

在 Codeigniter 中我们可以像这样使用它很容易理解。

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

回答by Waseem shah

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

回答by Sherin Green

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

使用codeigniter 3.0框架，有新功能可用于单独或where和where操作。即group by和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();

回答by Jatin Raikwar

Try using Query grouping

尝试使用 Query grouping

Link for it

链接它

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

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

回答by Shawn C

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

目前使用 CI2，您无法访问数据库类的查询构建器方法（$this->db->_compile_select() ）而不扩展数据库类并将方法的访问类型从私有更改为公共/受保护，这会扼杀能力构建子查询就像您尝试使用 ActiveRecord 类构建一样。使子查询像您尝试构建的那样的唯一方法是仅使用 db 查询方法

$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 Subquerysbut it's out of date and only works on CI 1.7 Hope that helps a bit.

在CI Subquerys 上有一篇关于这样做的博客文章，但它已经过时并且只适用于 CI 1.7 希望有所帮助。

回答by Piyin

I needed this for CodeIgniter 2, but I needed the values still escaped so csotelo's answerwasn'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:

我需要为 CodeIgniter 2 使用这个，但我需要仍然转义的值，所以csotelo 的答案对我不利，我不想像Shawn C 的答案那样重写整个查询，所以我最终这样做了：

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

回答by Dirk de Man

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

• 托芙·斯文森，12 岁
• 奥拉·斯文森，12 岁
• 任何名为 Svendson 的男性，12 岁
• 来自印度的任何人，名叫 Svendson，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.

Tove 看起来像一个男人的名字，所以选择性别是不必要的。Ola 看起来像一个女孩的名字，所以选择性别不仅没有必要，而且没有意义。您的查询将返回任何名为 Svendson 的 12 岁男性、来自印度的任何名为 Svenson 的 12 岁男性以及 Tove 和 Ola Svendson（如果他们是 12 岁）。

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

你为什么不想把它放在 () 括号之间？出于某种原因，你想用活动记录来完成它吗？