PHP RFC: MySQLi Execute Query

• Version: 0.1
• Voting Start: ?
• Voting End: ?
• RFC Started: 2022-04-04
• RFC Updated: 2022-04-04
• Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]
• Status: Draft
• First Published at: https://wiki.php.net/rfc/mysqli_execute_query
• GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc
• Implementation: From Kamil Tekiela (proof of concept)

Make parameterised MySQLi queries easier, with mysqli_execute_query($sql, $params).

This will further reduce the complexity of using parameterised queries - making it easier for developers to move away from mysqli_query(), and the dangerous/risky escaping of user values.

This new function is a simple combination of:

1. mysqli_prepare()
2. mysqli_execute()
3. mysqli_stmt_get_result()

It follows the original Draft RFC MySQLi Execute with Parameters, which proposed a single function to execute a parameterised query; and the Implemented RFC mysqli bind in execute, which addressed the difficulties with bind_param().

Using an example, assume we start with:

$db = new mysqli('localhost', 'user', 'password', 'database');
 
$sql = 'SELECT * FROM user WHERE name LIKE ? AND type IN (?, ?)';
 
$name = '%a%';
$type1 = 'admin';
$type2 = 'editor';

Before PHP 8.1, a typical parameterised query would look like this:

$statement = $db->prepare($sql);
$statement->bind_param('sss', $name, $type1, $type2);
$statement->execute();
 
foreach ($statement->get_result() as $row) {
    print_r($row);
}

Since PHP 8.1, we no longer have problems with binding by reference, or needing to specify the variable types via the first argument to bind_param(), e.g.

$statement = $db->prepare($sql);
$statement->execute([$name, $type1, $type2]);
 
foreach ($statement->get_result() as $row) {
    print_r($row);
}

The proposed function will simplify this even further, by allowing developers to write this in a one line foreach:

foreach ($db->execute_query($sql, [$name, $type1, $type2]) as $row) {
    print_r($row);
}

In pseudo-code it's basically:

function mysqli_execute_query(mysqli $mysqli, string $sql, array $params = null)
{
    $driver = new mysqli_driver();
 
    $stmt = $mysqli->prepare($sql);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {
        return false;
    }
 
    $stmt->execute($params);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {
        return false;
    }
 
    return $stmt->get_result();
}

The name was inspired by Doctrine\DBAL\Connection::executeQuery().

Because the implementation is effectively calling mysqli_stmt_get_result() last, while it will return false on failure, it will also return false for queries that do not produce a result set (e.g. UPDATE). Historically this has been addressed by using mysqli_errno(), but since 8.1 the Change Default mysqli Error Mode RFC was accepted, and Exceptions are used by default.

The implementation discards the mysqli_stmt object immediately, so you cannot re-issue a statement with new parameters. This is a rarely used feature, and anyone who would benefit from this (to skip running prepare again), can still use mysqli_prepare().

Cannot change mysqli_query() because it's second argument is $resultmode.

Cannot replace the deprecated mysqli_execute() function, which is an alias for mysqli_stmt_execute(), because it would create a backwards compatibility issue.

Because the Remove support for libmysql from mysqli RFC has been accepted, it makes it much easier to implement with mysqlnd.

None

PHP 8.2

None known

1. mysqli, adding a new function.

None known

None

None

Currently $mysqli->affected_rows and mysqli_affected_rows($mysqli) returns -1.

Because mysqli_stmt is not returned, it's not possible to use its properties:

1. int|string $affected_rows - see above
2. int|string $insert_id - can use $mysqli->insert_id or mysqli_insert_id($mysqli)
3. int|string $num_rows - also available on mysqli_result
4. int $param_count
5. int $field_count - also available on mysqli_result
6. int $errno - can use mysqli_errno($mysqli), $mysqli->errno
7. string $error - can use mysqli_error($mysqli), $mysqli->error
8. array $error_list - can use mysqli_error_list($mysqli), $mysqli->error_list
9. string $sqlstate - can use mysqli_sqlstate($mysqli), $mysqli->sqlstate
10. int $id

N/A

N/A

Accept the RFC

TODO

From Kamil Tekiela (proof of concept)

N/A

TODO