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 CodeIgniter's user guide.
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__');
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();
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.
select username from users inner join locations on users.locationid = locations.locationid where countryid = '$'