Search code examples
codeignitercodeigniter-2

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'
Solution

  • 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.