confusing query

Question

I am really sorry to ask this silly question but I am a bit confused about the query.

$this->db->select('user_package.status,user_package.remaining,user_package.date,package.pname,package.pcount');
$this->db->join('user_package','user_package.pid=package.pid','left');
$this->db->where('user_package.status','Active');
$this->db->or_where('user_package.status','Pending');
$this->db->where('user_package.uid',$rows->uid);
$user_package=$this->db->get('package');

and here is the similar query in mysql panel which i run for result to check the out put

Select up.status,up.remaining,up.date,p.pname,up.uid,p.pcount FROM user_package up LEFT JOIN package p ON up.pid=p.pid where up.status="Active" OR up.status="Pending" AND up.uid=7

and here is the output of the query

mysql output

What i want is that find only those rows whose uid is 7 and check if their status is Pending or Active. but the output is different. it takes out Pending and Active records with 7 and 8 uid.

What you think is `(where up.status="Active" OR up.status="Pending") AND up.uid=7` is probably being interpreted as `where up.status="Active" OR (up.status="Pending" AND up.uid=7)` — Patashu, Mar 19 '13 at 03:04
Have a look at [this stackoverflow post](http://stackoverflow.com/questions/6470267/grouping-where-clauses-in-codeigniter) — Simon MᶜKenzie, Mar 19 '13 at 03:17
try to echo the query . use this , echo $this->db->last_query(); and manipulate the query accordingly — Vaibhav, Mar 19 '13 at 09:29

score 0 · Answer 1 · answered Mar 19 '13 at 03:19

Instead of using $this->db->where try using $this->db->where_in

This should limit the results to only the IDs you require.

e.g.

$this->db->select('user_package.status,user_package.remaining,user_package.date,package.pname,package.pcount');
$this->db->join('user_package','user_package.pid=package.pid','left');
$this->db->where('user_package.status','Active');
$this->db->or_where('user_package.status','Pending');
$this->db->where_in('user_package.uid',$rows->uid);
$user_package=$this->db->get('package');

Muhammad Raheel · Answer 2 · 2013-03-19T03:43:49.017

You have to do it like this

$select =   array(
                'up.status',
                'up.remaining',
                'up.date',
                'p.pname',
                'up.uid',
                'p.pcount'  
            );
$status =   array('Active','Pending');      

$this->db
        ->select($select)
        ->from('user_package up')
        ->join('package p','up.pid = p.pid','left')
        ->where_in('up.status',$status)
        ->where('up.uid',$rows->uid)
        ->get();

confusing query

2 Answers2