+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query Binding Using IN Clause with either string array or string list - Not Using Active Record (/showthread.php?tid=59671)
Query Binding Using IN Clause with either string array or string list - Not Using Active Record - El Forum - 11-03-2013
[eluser]Unknown[/eluser]
CodeIgniter cannot seem to handle Query Binding using IN. Below is an example which seems to fail. I have tried several ways of getting $arrayOfIds below into the correct syntax for the id IN (?) with no luck. If I do not query bind, it works. There has to be answer.
Example:
Code:
// this is actually being passed in as argument
$arrayOfIds = array('A0000-000000000001','B0000-000000000001','C0000-000000000001');
$params = array();
array_push($params,1); // for the status
array_push($params, "'" . implode("','",$arrayOfIds) . "'"); // for the id in
$sql = "SELECT name FROM my_table WHERE status = ? AND id IN (?) ";
$query = $this->db->query($sql,$params);Solution:
CodeIgniter appears to escape all bound query params. So you end up with IN ('\'A0000-000000000001,B0000-000000000001,C0000-000000000001'\'') -OR- ('\'A0000-000000000001\',\'B0000-000000000001\',\'C0000-000000000001\''). However, as suggested by Yan Berk on StackOverflow, you can create a bind variable containing the appropriate amount of bind params (results in "?,?,?) and the merge the id array to the query param array.
Code:
$arrayOfIds = array('A0000-000000000001','B0000-000000000001','C0000-000000000001');
$params = array();
array_push($params, 1);
$params = array_merge($params, $arrayOfIds);
$in_string = str_replace(' ', ',', trim(str_repeat("? ", count($arrayOfIds))));
$sql = "SELECT name FROM my_table WHERE status = ? AND id IN (".$in_string.")";
$query = $this->db->query($sql, $params);