我正在尝试在本地数据库中访问数据(在我的VM上),我必须使用CodeIgniter的查询构建类来获取数据。我有这个查询,我在SQL中弄清楚:
select message, created
from logs
where username = 'user'
and (
created > '1487695796'
and created < '1487782196'
)
and (
message = 'login failure'
or message = 'login success'
or message = 'log out'
)
order by created asc
我最大的问题是如何在CodeIgniter中的get_where语句中链接'and and'and'?我看过并看到我可以将物品放在"哪里"部分的数组中,但是我还没有看到如何将其放入'或'或'的部分(因为阵列中的所有内容都是'and'。我必须使用get_where(不能做'get-> where'),所以如果有办法做到这一点,请让我知道!
感谢您抽出宝贵的时间阅读我的问题!
自Codeigniter 3.0查询构建器类支持查询分组
来自文档:
$this->db->select('*')->from('my_table')
->group_start()
->where('a', 'a')
->or_group_start()
->where('b', 'b')
->where('c', 'c')
->group_end()
->group_end()
->where('d', 'd')
->get();
// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'
编辑:在您的示例中,您将使用:
$this->db->select('message, created')->from('logs')
->where('username', 'user')
->group_start()
->where('created >', '1487695796')
->where('created <', '1487782196')
->group_end()
->group_start()
->where('message', 'login failure')
->or_where('message', 'login success')
->or_where('message', 'log out')
->group_end()
->order_by('created', 'ASC')
->get();
或
$this->db->select('message, created')
->group_start()
->where('created >', '1487695796')
->where('created <', '1487782196')
->group_end()
->group_start()
->where('message', 'login failure')
->or_where('message', 'login success')
->or_where('message', 'log out')
->group_end()
->order_by('created', 'ASC')
->get_where('logs', array('username' => 'user') );
// both generate:
//SELECT `message`, `created`
//FROM `logs`
//WHERE `username` = 'user'
//AND ( `created` > '1487695796' AND `created` < '1487782196' )
//AND ( `message` = 'login failure' OR `message` = 'login success' OR `message` = 'log out' )
//ORDER BY `created` ASC
要检查Codeigniter生成的查询是否匹配您的SQL,您可以使用:
echo $this->db->last_query(); // echos last query string
请尝试此
$q=$this->db
->select('message,created')
->where('username','user')
->where('created >','1487695796')
->where('created <','1487782196')
->where('message','login failure')
->or_where('message','login success')
->or_where('message','log out')
->order_by('created, ASC')
->get('logs');
return $q->result();
$this->db->select('message, created');
$this->db->where(['username' => ''user'', 'created >' => '1487695796', 'created <' => '1487782196']);
$this->db->where("(message='login failure' OR message='login success' OR message='log out')", NULL, FALSE);
$this->db->order_by('created', 'ASC');
$query = $this->db->get('logs');
return $query->result();