Search code examples
phpmysqlisql-in

How do you use IN clauses with mysqli prepared statements

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?

Solution

  • 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