Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Mysql WHERE IN clause in codeigniter

I have the following mysql query. Could you please tell me how to write the same query in Codeigniter's way ?

SELECT * FROM myTable 
         WHERE trans_id IN ( SELECT trans_id FROM myTable WHERE code='B') 
         AND code!='B'
like image 612
black_belt Avatar asked Jun 13 '12 21:06

black_belt


People also ask

How to use where in query in codeIgniter?

$this->db->get_where() Identical to the above function except that it permits you to add a “where” clause in the second parameter, instead of using the db->where() function: $query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset);

How use distinct in join query in codeIgniter?

You can use below mentioned query. $query = $this->db->group_by('category_master. Category_Id,business_profile_details. Business_Id');

How to make JOIN in codeIgniter?

If you want to use the right outer join in Codeigniter OR right join in Codeigniter, pass the third parameter in join() function/method. $this->db->select('*') ->from('users') ->join('comments','comments. user_id = users. u_id','right')//this is the right join in codeigniter ->get();


2 Answers

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this
Go to system/database/DB_active_rec.php Remove public or protected keyword from these functions

public function _compile_select($select_override = FALSE)
public function _reset_select()

Now subquery writing in available And now here is your query with active record

$this->db->select('trans_id');
$this->db->from('myTable');
$this->db->where('code','B');
$subQuery = $this->db->_compile_select();

$this->db->_reset_select();
// And now your main query
$this->db->select("*");
$this->db->where_in("$subQuery");
$this->db->where('code !=', 'B');
$this->db->get('myTable');

And the thing is done. Cheers!!!
Note : While using sub queries you must use

$this->db->from('myTable')

instead of

$this->db->get('myTable')

which runs the query.
Watch this too

How can I rewrite this SQL into CodeIgniter's Active Records?

Note : In Codeigntier 3 these functions are already public so you do not need to hack them.

like image 77
Muhammad Raheel Avatar answered Oct 17 '22 00:10

Muhammad Raheel


$data = $this->db->get_where('columnname',array('code' => 'B'));
$this->db->where_in('columnname',$data);
$this->db->where('code !=','B');
$query =  $this->db->get();
return $query->result_array();
like image 32
areeb Avatar answered Oct 17 '22 00:10

areeb