准备语句WHERE ... IN（...）with array parameter

Question

In my case, I'm deleting from multiple tables using LEFT JOIN and need to provide an array of question ids to delete. The array of question ids is $questions_to_delete.

Not being able to bind an array as a parameter through mysqli is kind of a pain, and I've looked through a few SO questions to arrive at this:

$params = implode(',', array_fill(0, count($questions_to_delete), '?'));
$types = array_fill(0, count($questions_to_delete), 'i');
$delete_questions = $mysqli->prepare('DELETE    ...
                                          FROM questions
                                          LEFT JOIN ...
                                          WHERE questions.id IN ('.$params.')');

call_user_func_array(array(&$delete_questions, 'bind_param'), array_merge($types, $questions_to_delete));
$delete_questions->execute();
$delete_questions->close();

The error I'm getting is

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables

I noticed that some answers used &$delete_questions versus $delete_questions, but I'm stumped as to what PHP is complaining about.

Answer 1

I wasn't merging $types and $questions_to_delete correctly! In my original code:

// Produces ['i', 'i', 'i', ...]
$types = array_fill(0, count($questions_to_delete), 'i');

// The argument (array_merge) is then ['i', 'i', ..., 'id1', 'id2', ...]
call_user_func_array(array($delete_questions, 'bind_param'), array_merge($types,$questions_to_delete));

What eventually worked for me was:

// Produces 'iii..'
$types = str_repeat('i', $questions_to_delete);

// The argument (array_merge) is then ['iii...', 'id1', 'id2', ...]
call_user_func_array(array($delete_questions, 'bind_param'), array_merge(array($types),$questions_to_delete));

So the types of the parameters need to be a String at the beginning of the parameters array.

I don't really understand how call_user_func_array handles array(mysqli_stmt, 'bind_param') as a callable, or why the parameters have to be constructed this way, and I'd like to see if anyone can come up with an explanation!