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

implode would be the simplest solution

$idList = array('id1','id2','id3');
$query = "SELECT id,name FROM user WHERE id IN (?)";
$stmt = $db->prepare($query);
$_param = is_array($idList) ? implode(',',$idList) : $idList;
$stmt->bind_param(1, $_param);

Answer 2

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

No you cannot.

For your situation, you should build the query string programmatically. If you are guaranteed it will always be three values, you could add three markers to the SQL then bind via looping over the array.

Answer 3

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.