Prepared statement 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.

Should check a similar problem I faced on stackoverflow.com/questions/11864911/… — M. Ahmad Zafar
– M. Ahmad Zafar, Commented Aug 8, 2012 at 19:24

actaeon · Accepted Answer · 2012-08-08 19:20:24Z

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!

Collectives™ on Stack Overflow

Prepared statement WHERE ... IN (...) with array parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related