Using Mysql WHERE IN clause in codeigniter

Question

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'

Yes, I have managed to use it successfully few minutes ago.. :) Thanks :) — black_belt, Jun 15 '12 at 06:01
@raheelshan. Would you please check this post. http://stackoverflow.com/q/11045611/1209690 Thanks :) — black_belt, Jun 15 '12 at 06:28

score 25 · Accepted Answer · edited May 23 '17 at 12:18

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.

score 17 · Answer 2 · answered Apr 18 '13 at 14:20

17

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

answered Apr 18 '13 at 14:20

areeb

424
3
9

score 7 · Answer 3 · answered Jun 14 '12 at 08:36

Try this one:

$this->db->select("*");
$this->db->where_in("(SELECT trans_id FROM myTable WHERE code = 'B')");
$this->db->where('code !=', 'B');
$this->db->get('myTable');

Note: $this->db->select("*"); is optional when you are selecting all columns from table

score 1 · Answer 4 · edited Jun 13 '12 at 21:23

1

try this:

return $this->db->query("
     SELECT * FROM myTable 
     WHERE trans_id IN ( SELECT trans_id FROM myTable WHERE code='B') 
     AND code!='B'
     ")->result_array();

Is not active record but is codeigniter's way http://codeigniter.com/user_guide/database/examples.html see Standard Query With Multiple Results (Array Version) section

edited Jun 13 '12 at 21:23

Colin Brock

21,267
9
46
61

answered Jun 13 '12 at 21:20

mvpasarel

775
5
13

score 0 · Answer 5 · edited Oct 14 '21 at 06:49

0

you can use a simpler approach, while still using active record

$this->db->select("a.trans_id ,a.number, a.name, a.phone")
$this->db->from("Name_Of_Your_Table a");
$subQueryIn = "SELECT trans_id FROM Another_Table";
$this->db->where("a.trans_id in ($subQueryIn)",NULL);

edited Oct 14 '21 at 06:49

Dharman

30,962
25
85
135

answered Oct 14 '21 at 06:44

Hasan

32
4

Using Mysql WHERE IN clause in codeigniter

5 Answers5

Linked