PHP 8.2: MySQLi: New mysqli_execute_query function and mysqli::execute_query method

Version8.2
TypeNew Feature

In PHP 8.2, the MySQLi extension provides a more straight-forward approach to prepare, bind, execute, and retrieve results from an SQL with a new mysqli_execute_query function for procedural API and a new mysqli::execute_query method.

The new mysqli_execute_query function and mysqli::execute_query method accept an SQL query and optionally an array of parameters. The query will be prepared, bound (if parameters are passed), executed within the function/method. It returns a mysqli_result object on successful queries, or false if the query was unsuccessful.

In a simplified form, mysqli_execute_query/mysqli::execute_query is a shortcut to the mysqli::prepare(), mysqli_stmt::bind_param(), mysqli_stmt::execute, and mysqli_stmt::get_result() calls.

Without the mysqli_execute_query function, a parameterized query is executed with a lot of boilerplate code like this:

$query = 'SELECT uid, username FROM users WHERE uid = ?';
$statement = $connection->prepare($query);
$statement->bind_param('s', $uid);
$statement->execute();
$result = $statement->get_result();

With the addition of mysqli_execute_query, the snippet above could be greatly simplified:

$query = 'SELECT uid, username FROM users WHERE uid = ?';
$result = $mysqli->execute_query($query, [$uid]);

In a procedural API example:

  $query = 'SELECT uid, username FROM users WHERE uid = ?';
- $statement = mysqli_prepare($connection, $query);
- mysqli_stmt_bind_param($statement, 's', $uid);
- mysqli_stmt_execute($statement);
- $result = mysqli_stmt_get_result($statement);
+ $result = mysqli_execute_query($connection, $query, $uid);

In PHP 8.1, it is possible to bind parameters in execute() directly, bypassing the bind_param() call. PHP 8.1: MySQLi: Bind in Execute

mysqli_execute_query function 

/**
 * Prepares an SQL statement, binds parameters, executes, and returns the result.
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query  The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.
 */
function mysqli_execute_query(mysqli $mysql, string $query, ?array $params = null): mysqli_result|bool {}

mysqli::execute_query method 

class mysqli {  
  // ...  

  /**  
 * Prepares an SQL statement, binds parameters, executes, and returns the result. * @param string $query The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.  
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.  
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.  
 */  
  public function execute_query(string $query, ?array $params = null): mysqli_result|bool {}  
}

Polyfill

It is possible to polyfill the new mysqli_execute_query function by declaring the function in user-land PHP. On classes that extend mysqli_stmt class, it is possible to add the new execute_query() method too.

Following is an example of mysqli_execute_query function implementation in user-land PHP compatible in PHP 8.1 and later.

/**
 * Prepares an SQL statement, binds parameters, executes, and returns the result.
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query  The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.
 */
function mysqli_execute_query(mysqli $mysqli, string $sql, ?array $params = null): mysqli_stmt|bool {
  $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();
}

Following is a user-land PHP polyfill that should work in PHP 7.1 and later:

/**  
 * Prepares an SQL statement, binds parameters, executes, and returns the result. * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.  
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.  
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.  
 */  
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;  
  }  

  if (!empty($params)) {  
    mysqli_stmt_bind_param($stmt, str_repeat('s', count($params)), ...$params);  
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {  
      return false;  
    }  
  }  

  $stmt->execute();  
  if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {  
    return false;  
  }

  return $stmt->get_result();  
}

Related Changes

Backwards Compatibility Impact

The new mysqli_execute_query function and mysqli::execute_query method can be implemented in user-land PHP code on PHP 8.1 in a straight-forward manner. It is possible to implement this functionality in PHP versions older than PHP 8.1 too, but requires more steps due to the lack of PHP 8.1's Bind in Execute feature.

Applications that declare their own mysqli_execute_query function in the global namespace will result in an error because PHP declares the function in PHP 8.2.

RFC Discussion Implementation