multiple calls to $stmt->bind_param

Question

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?

Answer 1

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();