"SELECT ... IN (SELECT ...)" query in CodeIgniter

Question

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!

Answer 1

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__');

Answer 2

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

Answer 3

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