CodeIgniter Query Binding Using IN Clause with String array or string list

Question

Below is an example which fails. 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.

Note: We are not using Active Record.

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

Answer 1

You need to build the params array differently and add as many question marks as the size of arrayOfIds.

EDIT: The question marks are generated dynamically according to the array size.

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