Codeigniter Active Record issue (where and multiple 'AND' 'OR')

Question

could somebody tell me how to generate the following query with codeigniter active record?

SELECT * FROM `tbl_admins` WHERE (`email`='ci@php.net' OR `uname`='user') AND `upass`='SecretePass';

not writing the query, I mean passing different values through arrays... or CI Active Record

thank you

Answer 1

I believe you can do this in a few ways. First, I am assuming you are passing in an array to the model function and that array is $filters.

Option One

$this->db->select('*');
$this->db->from('tbl_admins');
$this->db->where('email', $filters["email"]);
$this->db->or_where('uname', $filters["email"]); 
$this->db->where('upass', $filters["password"]);

Option Two

$this->db->select('*');
$this->db->from('tbl_admins');
$this->db->where("(email = '$filters["email"]' OR uname = '$filters["email"]') AND upass = '$filters["password"]'");

I did not test, so you may need to adjust the variables in option two's where a bit. But that is fairly close to what you want.

Here is another example of a similar Stack Overflow question.

Answer 2

finally I write it this way...

$conditions = '(`col1`="'.$var1.'" OR `col2`="'.$var1.'") AND `col3`="'.$var2.'"';          
 $query = $this->db->get_where('table',$conditions);
$result = $query->result();

And it works :)

Answer 3

You can use the group_start() and group_end() functionality (from active record) for grouping the relevant where clauses in separate groups. Here is the example of the syntax:

$query= $this->db->select('*')
        ->group_start()
        ->or_like($likeColumns)
        ->group_end()
        ->where($whereColumn, $whereValue)
        ->get($table);