使用CodeIgniter get_where链接"and"和"or"语句 [英] Using CodeIgniter get_where to chain 'and' and 'or' statements

问题描述

我试图访问本地数据库(在我的vm上)中的数据，并且必须使用CodeIgniter的查询构建类来获取数据.我有这个查询，我已经在sql中找到了

I am trying to access data in a local database (on my vm), and I have to use CodeIgniter's query building classes to get the data. I have this query which I have figured out in 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语句中链接和"和或"?我已经看过并且看到我可以将内容放入"WHERE"部分的数组中，但是我还没有看到如何将内容放入或"中(因为数组中的所有内容都是与".我必须使用get_where(不能执行"get-> where")，所以如果有办法，请告诉我！

My biggest question is how can I chain the 'and's and 'or's in a get_where statement in CodeIgniter? I have looked and saw I could put things in an array for the 'WHERE' portion, but I haven't seen how I can place things in for the 'or's (since everything in the array is an 'and'. I MUST use get_where (can't do 'get->where'), so if there is a way to do it, please let me know!

感谢您抽出宝贵的时间阅读我的问题！

Thank you for taking the time to read my question!

推荐答案

从Codeigniter 3.0开始，查询构建器类支持查询分组

Since Codeigniter 3.0 the query builder class supports Query grouping

来自文档:

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

修改:在您的示例中，您将使用:

in your example you would use:

$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匹配，可以使用:

to check if the Codeigniter generated query matches your SQL, you can use:

echo $this->db->last_query(); // echos last query string

这篇关于使用CodeIgniter get_where链接"and"和"or"语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！