phpmysqliprepared-statementbindparam

multiple calls to $stmt->bind_param

I'm in a situation where I want to build a code which gets $bindParam variable in this format:

$bindParams = [$type1 => $param1, $type2 => $param2, ... ]

I wanna build some code that dynamically adds that parameters to the prepared statement.
This is the code which I built so far :

$mysql = new mysqli("localhost", "root", "", "db1");
$stmt = $mysql->prepare($sql);
foreach($bindParams as $type => $data) {
    $stmt->bind_param($type, $data);
}
$stmt->execute();
$result = $stmt->get_result();
// and after perhaps twiddling with the result set, but this is not the case .....

For your instance 

$sql = "INSERT INTO table1 (name, age) VALUES (?,?);"

and 

$bindParams = ["s" => "hello", "i" => 15]

This does not always have this structure and it can change to for example $bindParams = ["s" => "hello", "i" => 15, "d" => 22.5] and so the $sql changes respectively.

After the first time the compiler heads to $stmt->bind_param($type, $data); firefox flushes this error:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in D:\PHP\tr.php on line 23

I know PDO support that as stated here at the end of the page. but perhaps as you might expect Im not a fan of PDO so ;)
My other option is to use the eval() workarounds available in php but thats out of what I might think of.

Is there another way to do this?

Solution

  • Sadly mysqli doesn't support this. Calling the function over and over again overwrites the values, so you're only binding one param when you clearly have more.

    There's a couple of ways to get around this

    1. Switch to PDO. You can make one bind per function call with that

    2. Bind the params as one aggregate using call_user_func_array

       $sqltype = '';
 $sqldata = [];
 foreach($bindParams as $type => $data) {
     $sqltype .= $type;
     $sqldata[] = &$data; // MUST be a reference
 }
 array_unshift($sqldata, $sqltype); // prepend the types
 call_user_func_array([$stmt, 'bind_param'], $sqldata);

    PHP 8.1 and later

    An even easier solution is to use a convention in PHP 8.1 where you place your data into an indexed (unkeyed) array and let mysqli bind it all as strings

    $stmt->execute([$firstname, $lastname, $userid]);

    Functionally equivalent to

    $stmt->bind_param('sss', $firstname, $lastname, $userid);
$stmt->execute();