Php mysqli WHERE IN (?,?,? ...)

Question

According to http://us2.php.net/manual/en/mysqli-stmt.bind-param.php , different types are:

i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets 

However, how can you handle this:

 ->prepare("SELECT blabla FROM foo WHERE id IN (?)") 

Where? there will be a list of identifiers. There may be one or more elements:

 $ids = "3,4,78"; ->bind_param('s',$ids); 

Is it possible?

I would like to use prepared instructions because it will be executed in a loop.

Answer 1

The correct syntax will be

 ->prepare("SELECT blabla FROM foo WHERE id IN (?, ?, ?)") 

for 3 elements in this array, for example. Then you need to bind each of these elements using bind_param() .

If you do not have a guarantee of the size in the array, you need to write a couple of helper functions to generate your SQL with the appropriate number of "?" and bind statements.

Answer 2

If you have a list of variables that differentiates each call you want to associate with an IN -statement in size, the easiest way would be to generate the SQL string programmatically and use a loop to bind the variables:

 /** * @param array $values * @param mysqli $db * @return mysqli_stmt */ function bindInValues(array $values, mysqli $db) { $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)', implode(', ', array_fill(0, count($values), '?')) ); $stmt = $db->prepare($sql); foreach ($values as $value) { $stmt->bind_param('s', $value); } return $stmt; } 

If you like call_user_func_array , you can use a dynamic method call and go without a loop.

 /** * @param array $values * @param mysqli $db * @return mysqli_stmt */ function bindInValues(array $values, mysqli $db) { $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)', implode(', ', array_fill(0, count($values), '?')) ); $stmt = $db->prepare($sql); array_unshift($values, implode('', array_fill(0, count($values), 's'))); call_user_func_array(array($stmt, 'bind_param'), $values); return $stmt; } 

Answer 3

How to do it:

 $sql = sprintf("SELECT blabla FROM foo WHERE id IN(%s) ", $ids); $stmt = $mysqli->prepare($sql); if (!$stmt) { $this->throwException(); } if (!$stmt->execute()) { $this->throwException(); } 

If this is not good, tell me why I can learn from my mistakes. Thanks!

Answer 4

I think I found the answer to my question:

 ->prepare("SELECT stuff FROM table_name WHERE id IN (?)"); $itemList = implode(',',$items); $children->bind_param('s',$itemList); 

It seems to work fine when using a string with coma-separated values. I am still checking if the results are really accurate ...