php 如何使用 CodeIgniter 的 ActiveRecord 选择列值不是 NULL 的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2489453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select rows where column value IS NOT NULL using CodeIgniter's ActiveRecord?
提问by rebellion
I'm using CodeIgniter's Active Record class to query the MySQL database. I need to select the rows in a table where a field is not set to NULL:
我正在使用 CodeIgniter 的 Active Record 类来查询 MySQL 数据库。我需要选择字段未设置为 NULL 的表中的行：
$this->db->where('archived !=', 'NULL');
$q = $this->db->get('projects');
That only returns this query:
那只返回这个查询：
SELECT * FROM projects WHERE archived != 'NULL';
The archivedfield is a DATEfield.
该archived场是一个DATE场。
Is there a better way to solve this? I know I can just write the query myself, but I want to stick with the Active Record throughout my code.
有没有更好的方法来解决这个问题？我知道我可以自己编写查询，但我想在我的代码中坚持使用 Active Record。
回答by zerkms
where('archived IS NOT NULL', null, false)
回答by None
The Active Record definitely has some quirks. When you pass an array to the $this->db->where()function it will generate an IS NULL. For example:
Active Record 肯定有一些怪癖。当您将数组传递给$this->db->where()函数时，它将生成一个 IS NULL。例如：
$this->db->where(array('archived' => NULL));
produces
产生
WHERE `archived` IS NULL
The quirk is that there is no equivalent for the negative IS NOT NULL. There is, however, a way to do it that produces the correct result and still escapes the statement:
奇怪的是，负数没有等价物IS NOT NULL。但是，有一种方法可以产生正确的结果并且仍然可以转义该语句：
$this->db->where('archived IS NOT NULL');
produces
产生
WHERE `archived` IS NOT NULL
回答by Come2Daddy
Null must not be set to string...
Null 不能设置为字符串...
$this->db->where('archived IS NOT', null);
It works properly when null is not wrapped into quotes.
当 null 未包含在引号中时，它可以正常工作。
回答by Rodrigo Prazim
CodeIgniter 3
代码点火器 3
Only:
仅有的：
$this->db->where('archived IS NOT NULL');
The generated query is:
生成的查询是：
WHERE archived IS NOT NULL;
$this->db->where('archived IS NOT NULL',null,false); << Not necessary
$this->db->where('archived IS NOT NULL', null, false); << 没有必要
Inverse:
逆：
$this->db->where('archived');
The generated query is:
生成的查询是：
WHERE archived IS NULL;
回答by Smith
$this->db->or_where('end_date IS', 'NULL', false);
$this->db->or_where('end_date IS', 'NULL', false);
回答by Pavan K
You can do (if you want to test NULL)
你可以做（如果你想测试NULL）
$this->db->where_exec('archived IS NULL)
If you want to test NOT NULL
如果你想测试 NOT NULL
$this->db->where_exec('archived IS NOT NULL)
回答by Raza Rafaideen
Much better to use following:
更好地使用以下内容：
For is not null:
对于不为空：
where('archived IS NOT NULL', null);
For is null:
为空：
where('archived', null);
回答by Dave Strickler
And just to give you yet another option, you can use NOT ISNULL(archived)as your WHERE filter.
为了给您提供另一种选择，您可以将其NOT ISNULL(archived)用作 WHERE 过滤器。
回答by Lirio Push
Codeigniter generates an "IS NULL" query by just leaving the call with no parameters:
Codeigniter 生成一个“IS NULL”查询，只留下不带参数的调用：
$this->db->where('column');
The generated query is:
生成的查询是：
WHERE `column` IS NULL
回答by Amir Qayyum Khan
One way to check either column is null or not is
检查任一列是否为空的一种方法是
$this->db->where('archived => TRUE);
$q = $this->db->get('projects');
in php if column has data, it can be represent as True otherwise False To use multiple comparison in where command and to check if column data is not null do it like
在 php 中，如果列有数据，则可以表示为 True 否则为 False 要在 where 命令中使用多重比较并检查列数据是否不为空，请执行以下操作
here is the complete example how I am filter columns in where clause (Codeignitor). The last one show Not NULLCompression
这是我如何在 where 子句（Codeignitor）中过滤列的完整示例。最后一个显示Not NULLCompression
$where = array('somebit' => '1', 'status' => 'Published', 'archived ' => TRUE );
$this->db->where($where);
