How do you use IN clauses with mysqli prepared statements

Question

I’m moving some old code over to the new msqli interface using prepared statements, I’m having trouble with SQL statements containing the IN clause. I would just normally do this:

$ids = '123,535,345,567,878'
$sql = "SELECT * FROM table WHERE id IN ($ids)";
$res = mysql_query($sql);

Converting this to mysqli and prepared statements I have tried a number of solutions:

$ids = '123,535,345,567,878'
$ids = implode($ids,',');
$result = $msqli->prepare("SELECT foo,blar FROM table WHERE id IN (?));
$result->bind_param("i", $ids);
$result->execute();

The above fails and calculating the number of elements in the array and altering number of question marks in the SQL string and calling bind_parm for each element in the array also fails. Just using the comma separated string also fails.

I can find no good documentation in Google on this, so how have you solved the problem?

Answer 1

It's not possible to bind a list of variable length to a single bound variable.

Similarly, if you were to bind the string $ids you'll actually end up with:

SELECT foo,blar FROM table WHERE id IN ('123,535,345,567,878')

(Note the quotes around the list of IDs).

Creating your own query with the right number of question marks and bound parameters should have actually worked - you may need to try that again and report on the actual error.

Alternatively, this may be one of those occasions where it's unfortunately necessary to hand-craft your own SQL and not use bound parameters.

Answer 2

Look at the answer to a similar question that has been asked here before (second code sample):

I have an array of integers, how do I use each one in a mysql query (in php)?

It boils down to:

• create the SQL string with the right amount of question marks
• use call_user_func_array() to bind your array to the query string