[codeigniter] "SELECT ... IN (SELECT ...)" query in CodeIgniter

I have a query similar to this:

SELECT username 
FROM users 
WHERE locationid IN 
  (SELECT locationid FROM locations WHERE countryid='$')

$ is a value I get from end user.

How could I run this query in CodeIgniter? I can't find a solution in CodeIgnite's user guide.

Thank you so much for your answers!

Regards!

Note that these solutions use the Code Igniter Active Records Class

This method uses sub queries like you wish but you should sanitize $countryId yourself!

$this->db->select('username')
         ->from('user')
         ->where('`locationId` in', '(select `locationId` from `locations` where `countryId` = '.$countryId.')', false)
         ->get();

Or this method would do it using joins and will sanitize the data for you (recommended)!

$this->db->select('username')
         ->from('users')
         ->join('locations', 'users.locationid = locations.locationid', 'inner')
         ->where('countryid', $countryId)
         ->get();

Look here.

Basically you have to do bind params:

$sql = "SELECT username FROM users WHERE locationid IN (SELECT locationid FROM locations WHERE countryid=?)"; 

$this->db->query($sql, '__COUNTRY_NAME__');

But, like Mr.E said, use joins:

$sql = "select username from users inner join locations on users.locationid = locations.locationid where countryid = ?"; 

$this->db->query($sql, '__COUNTRY_NAME__');

I think you can create a simple SQL query:

$sql="select username from user where id in (select id from idtables)";
$query=$this->db->query($sql);

and then you can use it normally.

Also, to note - the Active Record Class also has a $this->db->where_in() method.