Can I bind multiple values as a single parameter using MYSQLI and PHP?

Question

Imagine I have the following SQL query:

SELECT id,name FROM user WHERE id IN ('id1','id2','id3')

Now imagine I need the array of ids to be supplied by PHP. So I have something like this:

$idList = array('id1','id2','id3');
$query = "SELECT id,name FROM user WHERE id IN (?)";
$stmt = $db->prepare($query);
$stmt->bind_param(/*Something*/);

What can I replace /*Something*/ with to get the same results as the original query? Or do I need to put in 3 question marks in the query format? The only reason I don't want to do that is because the number of question marks is variable, so I would have to build the query string manually.

Answer 1

You can use PHP to write out the placeholders ? using str_repeat(), and then just bind_param all your params in a loop.

Just be careful of the trailing comma that str_repeat will return. Either rtrim() it or instead use array_fill() to create an array of repeating placeholders and then join() those to create your placeholders string.

$arrPlaceholders = array_fill(0, count($idList), '?') ;
$strPlaceholders = join(', ', $arrPlaceholders) ;

Then your query can be:

$query = "SELECT id,name FROM user WHERE id IN ($strPlaceholders)";

And you can bind your parameters in a loop.