Dynamic Parameter Binding with MySQLi

A problem that I encountered recently was that I needed to bind a dynamic number of parameters to a mysql query in php. I’m using MySQLi to handle the connection and my queries.

The short answer to my problem was the call_user_func_array function. Taken from the php.net documentation:

Call a user function given with an array of parameters

After toying around and looking at other examples I came up with the following piece of code.

$queryParams = array();

This was to be my array that would hold the parameter types and values.

foreach ($terms as $t) $queryTypes .="s";

In my case, all of my parameters were strings. Creating a string with the parameter types was as easy as creating a string with the correct number of S’s.

$queryParams[] = $queryTypes;

Now I’ve added the string of parameter types to my array as the first value.

foreach ($terms as $id => $term)

Opening my foreach loop to get all my parameters into the array

$queryParams[] = &$terms[$id];

For each of my parameters, a reference is added to my array.

call_user_func_array(array($query,'bind_param'),$queryParams);

Finally, the magic. With my array now containing the parameter types and a reference to each of the parameters, I can call call_user_func_array. This sends my $queryParams array as inputs for bind_param on my $query variable (which I’d already bound a prepared MySQLi statement to).

Now, with my dynamic number of parameters bound, I can execute and fetch as per usual.