How to pass a list of parameters contained in an array to bind_param?

Question

Using MySQLi I want to prepare, execute, and get results from an SQL statement with an IN function, such as this:

SELECT id FROM records WHERE isbn IN ( 'aaa', 'bbb', 'ccc' );

The parameters intended for IN are actually dynamic, so they will be in an array, differing in length with each run.

For one run, imagine the array is:

$list = array('aaa', 'bbb', 'ccc');

So the prepared statement would have to look like this, with three question marks:

SELECT id FROM records WHERE isbn IN ( ?, ?, ? );

Here's what I have:

$question_marks = str_repeat('?, ', count($list));
$question_marks = preg_replace('/, $/', '', $question_marks);

$stmt = $linkID->prepare("SELECT id FROM records WHERE isbn IN ($question_marks)");

$types = str_repeat('s', count($list));

$stmt->bind_param($types, $list); // this is not working (error below)

The error I get is:

mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

How to pass a list of parameters contained in an array to bind_param?

Answer 1

Using PHP 5.6, you can do this easily with the help of the unpacking Operator (...$var) and use get_result() instead of bind_result().

$stmt->bind_param($types, ...$list);
$stmt->get_result();