Combination of AND and OR clauses

Question

I want to implement the following mysql statement in CodeIgniter.

select * from table_name 
         where deleted = 0 and (id = "18" or name = "efgh" or imei = "1244");

I have written the following statements in CodeIgniter:

$this->db->where('deleted', 0);
$this->db->or_where('id', $this->table_name->id);
$this->db->or_where('imei', $this->table_name->imei);
$this->db->or_where('name', $this->table_name->name);
$result= $this->db->get('table_name')->result();

But these two statements are giving different outputs. Could any one please point out the error in the CodeIgniter statement? Thank you.

score 1 · Answer 1 · answered Jul 25 '13 at 07:37

Please use this :-

   $where = "deleted='0' AND (id='18' OR name='efgh' OR imei='1244')";
   $this->db->where($where);
   $result= $this->db->get('table_name')->result();

For more details You can visit :-

http://ellislab.com/codeigniter/user-guide/database/active_record.html

score 1 · Answer 2 · answered Jul 25 '13 at 17:17

1

I would use

$this->db->where('deleted', 0);
$this->db->where("(id='18' OR name='efgh' OR imei='1244')", NULL, FALSE);
$result = $this->db->get('table_name')->result();

Cheers,

answered Jul 25 '13 at 17:17

Vitor Bari

105
7

score 0 · Answer 3 · answered Jul 25 '13 at 07:36

The same issue happened to me before.

I coulnt figure out with $this->db function.

And i modify system/database/DB_active_rec.php _compile_select() function

Original: Line 1746

if (count($this->ar_like) > 0) {
  if (count($this->ar_where) > 0) {
    $sql .= "\nAND ";
  }
  $sql .= implode("\n", $this->ar_like);
}

My changes:

if (count($this->ar_like) > 0) {
  if (count($this->ar_where) > 0) {
    $sql .= "\nAND ("; // open parenthesis for where and like command
  }
  $sql .= implode("\n", $this->ar_like);
  if (count($this->ar_where) > 0) {
    $sql .= "\n)"; // close parenthesis and sample sql text > WHERE column = x AND (LIKE QUERIES)
  }
}

score 0 · Answer 4 · answered Jul 25 '13 at 07:39

0

yes activerecord has problem using or, if you use activerecord it will not add start and end brackets for or condition then compare with and your query will be like this

select * from table_name where deleted = 0 and id="18" or name = "efgh" or imei = "1244";

to use it

$where = 'deleted = 0 and (id="18" or name = "efgh" or imei = "1244")';

$this->db->where($where);

answered Jul 25 '13 at 07:39

umefarooq

4,540
1
29
38

Can we write $where with `$this->db->where` or `$this->db->or_where`? I dont like string query. – Bora Jul 25 '13 at 07:44
hi check this post can help you you can use both methods http://stackoverflow.com/questions/11186491/codeigniter-grouping-where-clause – umefarooq Jul 25 '13 at 08:28
they are not useful. They do modify system file too. Im searching code that without modification system files. – Bora Jul 25 '13 at 09:49

score 0 · Answer 5 · answered Jul 25 '13 at 07:43

There is two way to do the same thing.
first 
$condition = "deleted='0' AND (id='18' OR name='efgh' OR imei='1244')";
$this->db->where($condition);
$result= $this->db->get('table_name')->result();
 and 
write your costume query 

$this->db->query('YOUR QUERY HERE');

Hope it will help you.

Combination of AND and OR clauses

5 Answers5