Azotherian Azotherian -4 years ago 181
MySQL Question

Using CodeIgniter get_where to chain 'and' and 'or' statements

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


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!

Answer Source

Since Codeigniter 3.0 the query builder class supports Query grouping

from the docs:

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

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

or

$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

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

echo $this->db->last_query(); // echos last query string
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download