Warning: mysqli_stmt_bind_param(): Number of variables doesn’t match number of parameters in prepared statement

How to bind to variable number of variables in mysqli_stmt_bind_param() in one statement? mysqli_stmt_bind_param() itself can accept variable parameters. The prototype of mysqli_stmt_bind_param is:

mysqli_stmt_bind_param ( mysqli_stmt $stmt , string $types , mixed &$var1 [, mixed &$... ] ) : bool

You can see mysqli_stmt_bind_param can bind to different number of variables provided that the types parameter must be consistent with the number of variables to bind. If not like the following example, you will get the error: Warning: mysqli_stmt_bind_param(): Number of elements in type definition string doesn’t match number of bind variables.

mysqli_stmt_bind_param($stmt,"sssssss",$var1,$var2);

Here, we try to bind two variables $var1 and $var2, but we have seven “s” in the type definition string which results in the warning message. The number of variables provided to mysqli_stmt_bind_param also needs to be consistent with the number of placeholders in the prepared sql statement, otherwise we will get the “Warning: mysqli_stmt_bind_param(): Number of variables doesn’t match number of parameters in prepared statement”.

Sometimes, we want to bind to unknown number of variables in one mysqli_stmt_bind_param. This is because the sql statement may have uncertain number of placeholders. For example,

$sql="insert into mytable values ";
if($var1)
  $sql.="(?),";
if($var2)
  $sql.="(?),";
$sql=trim($sql,",");
$stmt = mysqli_prepare($connection,$sql);
mysqli_stmt_bind_param($stmt,"ss",$var1,$var2);

You can not pass all possible variables to  mysqli_stmt_bind_param, hoping it can deal with the cases that some variables are empty or do not exist. That may produce the “number of variables doesn’t match number of parameters in prepared statement” error. You can use multiple mysqli_stmt_bind_param statements to deal with different cases like this:

$sql="insert into mytable values ";
if($var1)
  $sql.="(?),";
if($var2)
  $sql.="(?),";
$sql=trim($sql,",");
$stmt = mysqli_prepare($connection,$sql);
if($var1&&!$var2)
  mysqli_stmt_bind_param($stmt,"s",$var1);
else if(!var1&&var2)
  mysqli_stmt_bind_param($stmt,"s",$var2);
else if(!$var1&&!$var2)
  mysqli_stmt_bind_param($stmt,"ss",$var1,$var2);

But if there are lots of variables, the combination explosion will make this method not feasible.

This post seems to provide a solution to this problem, in which the call_user_func_array function is used. Here is the example from php official website:

<?php
function foobar($arg, $arg2) {
    echo __FUNCTION__, " got $arg and $arg2\n";
}
class foo {
    function bar($arg, $arg2) {
        echo __METHOD__, " got $arg and $arg2\n";
    }
}


// Call the foobar() function with 2 arguments
call_user_func_array("foobar", array("one", "two"));

// Call the $foo->bar() method with 2 arguments
$foo = new foo;
call_user_func_array(array($foo, "bar"), array("three", "four"));
?>

I never realized why we should use call_user_func_array before because in the example, since you can directly call the foobar function as foobar(“one”,”two”), why bother to use  call_user_func_array? But in the mysqli_stmt_bind_param example, I got to know it is really useful in some cases. Now you can use single statement to replace the multiple callings of mysqli_stmt_bind_param:

$sql="insert into mytable values ";
$type="";
$params=array();
if($var1){
  $sql.="(?),";
  $type.="s";
  $params[]=$var1;
}
if($var2){
  $sql.="(?),";
  $type.="s";
  $params[]=$var2;
}
$sql=trim($sql,",");
$stmt = mysqli_prepare($connection,$sql);
$params=array_merge(array($stmt,$type),$params);
call_user_func_array("mysqli_stmt_bind_param",$params);

Here, we pass an array $params to call_user_func_array as the parameters of mysqli_stmt_bind_param to be called. Since the number of elements in $params is variable, the number of parameters passed to mysqli_stmt_bind_param is variable too. This sounds like a good solution but you will get this error when running the code:

Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given

you can convert the variables in params to references using the method mentioned in that post but thanks to the spread operator …, you can simplify the code as:

$sql="insert into mytable values ";
$type="";
$params=array();
if($var1){
  $sql.="(?),";
  $type.="s";
  $params[]=$var1;
}
if($var2){
  $sql.="(?),";
  $type.="s";
  $params[]=$var2;
}
$sql=trim($sql,",");
$stmt = mysqli_prepare($connection,$sql);
mysqli_stmt_bind_param($stmt,type,...$params);

The spread  operator will expand the array to multiple references to variables and pass them to mysqli_stmt_bind_param.

If you like my content, please consider buying me a coffee. Buy me a coffeeBuy me a coffee Thank you for your support!
Posted in

Leave a Reply