Compared to PDO, mysqli is bit more verbose in some place. Binding parameters for prepared statement is one of them. In PDO, you can just pass an array as a parameter to execute() method and it will bind those array elements as parameter for the SQL statement. But in mysqli, we have a separate method bind_param() for it, with some limitation.

Why bind_param is not enough?

bind_param() expects arguments by reference, so you can't pass any data directly to it without assigning to a variable first.

$stmt = $mysqli->prepare('SELECT name FROM users WHERE id = ?');
$stmt->bind_param('i', 100); // it won't work here
$stmt->execute();

// to fix it
$stmt = $mysqli->prepare('SELECT name FROM users WHERE id = ?');
$id = 100;
$stmt->bind_param('i', $id);
$stmt->execute();

Another thing is, you have to pass parameter types as first argument even if the type will be string in almost all of the time. Passing array of parameters to bind_param() is unnecessarily complex too.

execute() accepts a parameter now

From PHP 8.1, we can forget all the bind_param() jargon and just pass parameters to execute() method, similar to PDO.

$stmt = $mysqli->prepare('SELECT name FROM users WHERE id = ?');

$stmt->execute([100]);
// I didn't need to define any type
// Also, I didn't pass it by reference, it still works
// Wow

$params = [100, 'Active'];

$stmt = $mysqli->prepare('SELECT name FROM users WHERE id = ? AND status = ?');

$stmt->execute($params); // works too

Isn't it awesome?

But, what if I need to define parameter type?

We can still use bind_param() for those cases.

Limitations:

• execute() only accepts list array. What is list?
• Can't re-bind empty arrays.

RFC: https://wiki.php.net/rfc/mysqli_bind_in_execute