PHP RFC: MySQLi Execute with Parameters

• Version: 0.1
• Date: 2020-12-26
• Author: Craig Francis, craig#at#craigfrancis.co.uk
• Status: Draft
• Published at: https://wiki.php.net/rfc/mysqli_execute_parameters

Make mysqli easier to use with parameterised queries.

Using parameterised queries with mysqli is too complicated:

$db = new mysqli('localhost', 'test', 'test', 'test');
 
$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?';
 
$name = '%a%';
$type = 'admin';
 
$statement = $db->prepare($sql);
$statement->bind_param('ss', $name, $type);
$statement->execute();
 
$result = $statement->get_result();
 
while ($row = $result->fetch_assoc()) {
    print_r($row);
}

Note that variables need to be used in bind_param(), because values cannot be passed by reference.

It's difficult to run a query which changes the parameters (e.g. filter by type might not always be used).

Most queries are single run, so the ability to re-issue the same $statement is rarely needed.

Introduce mysqli::execute() (and replace the deprecated mysqli_execute() function) to make parameterised queries much easier, e.g.

$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?';
 
$parameters = ['%a%', 'admin'];
 
$result = $db->execute($sql, $parameters);
 
while ($row = $result->fetch_assoc()) {
    print_r($row);
}

We probably cannot change mysqli::query() because it's second argument is $resultmode.

Something like this:

<?php
 
class mysqli_rfc extends mysqli {
    function execute($sql, $parameters = []) {
        $statement = mysqli_prepare($this, $sql);
 
        $ref_types = '';
        foreach ($parameters as $key => $value) {
            $ref_types .= (is_int($value) ? 'i' : 's'); // 'd' for double, or 'b' for blob.
            $ref_values[] = &$parameters[$key];
        }
 
        array_unshift($ref_values, $ref_types);
 
        call_user_func_array([$statement, 'bind_param'], $ref_values);
 
        $statement->execute();
 
        return $statement->get_result();
    }
}

Where $sql should be a safe literal string.

None

Next PHP 8.x

Not sure

1. mysqli

Not sure

1. Reliably detecting the variable types: integer, double, string, blob.

Not sure

Not sure

Not sure

Yes / No?

A volunteer is needed to help with implementation.

N/A

N/A