CODEWITHSUNDEEP
phpcodeigniterwhere-clausequery-builderlogical-grouping
Joy
Joy

Reputation: 4473

Group OR conditions and add group with AND to WHERE clause using CodeIgniter query builder methods

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?

Upvotes: 0

Views: 107

Answers (5)

Vitor Bari
Vitor Bari

Reputation: 105

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,

Upvotes: 1

innovative kundan
innovative kundan

Reputation: 631

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.

Upvotes: 0

umefarooq
umefarooq

Reputation: 4574

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);

Upvotes: 0

Prasannjit
Prasannjit

Reputation: 795

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

Upvotes: 1

Bora
Bora

Reputation: 10717

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)
  }
}

Upvotes: 0

Related Questions