Dynamically bind_params for mysqli Prepared Statements

UPDATE 10/28/2018 – Thanks to Lars Krakhecke for spotting a typo in my for loop.

Personally, I avoid using mysqli whenever I can, but sometimes you find yourself working on a legacy application, or it’s the only library your team is familiar with. MySQLi, only has methods for connecting to a MySQL database and some of its methods have peculiar implementations that make them hard to use in large scale applications.

The Problem When calling bind_param() the first argument is string of ‘types’ where a character specifies the data type of a corresponding value. The number of types must match the number and order of values you’re binding. For example:

/* Connect to database */

$conn = mysqli_connect($db_server, $db_user, $db_pass, $db_name);
/* Prepare a statement */
$stmt = $conn->prepare("SELECT * accounts where type=? and balance > ?");

/* Bind the parameters to the statement
s = string
d = double (float)
*/

$stmt-bind_params('sd', $account_type, $min_balance);
$account_type = 'savings';
$min_balance = 100.00;
$stmt->execute();

/* The result of all savings accounts with a balance > 100.00 */
$result = $stmt->get_result();

Suppose I don’t want my code to be littered with hand-crafted unique bind_param calls, how could I create a function that will bind a variable list of parameters? Thankfully, I stumbled on

this clever solution that uses the  call_user_func_array function.

Solution The call_user_func_array function allows you to specify a callable function (or method) and pass an array of arguments to be fed to the callable. Since I want to use the mysqli_stmt bind_params function I pass as the first argument an array containing my stmt object and the method name I want to call as a string. The second argument is another array. This second array holds my ‘type string’ as its first element and then my values to bind as succeeding elements. The elements in this second array must be passed as references.

$typeString = 'sd';
$vals = array('savings', 100.00);
$valCount = count($vals);

/* Populate args with references to values */
$args = array(&$typeString);
for ($i=0; $i < $valCount; $i++){
    $args[] = &$vals[$i];
}

/* call $stmt->bind_params() using $args as its parameter list */
call_user_func_array( array($stmt, 'bind_param'), $args);

Using this technique I can write a function that will bind any number of arguments to a statement and have only one place that I need to manage when it comes to catching exceptions or handling other errors.