Dynamic Parameter Binding with MySQLiIN PHP
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.
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.