====== 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

===== Introduction =====

Make //mysqli// easier to use with parameterised queries.

===== The Problem =====

Using parameterised queries with //mysqli// is too complicated:

<code php>
$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);
}
</code>

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.

===== Proposal =====

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

<code php>
$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);
}
</code>

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

===== Rough Implementation =====

Something like this:

<code php>
<?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();
    }
}
</code>

Where //$sql// should be a [[https://wiki.php.net/rfc/is_literal|safe literal string]].

===== Backward Incompatible Changes =====

None

===== Proposed PHP Version(s) =====

Next PHP 8.x

===== RFC Impact =====

==== To SAPIs ====

Not sure

==== To Existing Extensions ====

  - mysqli

==== To Opcache ====

Not sure

===== Open Issues =====

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

===== Alternatives =====

Not sure

===== Unaffected PHP Functionality =====

Not sure

===== Future Scope =====

Not sure

===== Proposed Voting Choices =====

Yes / No?

===== Patches and Tests =====

A volunteer is needed to help with implementation.

===== Implementation =====

N/A

===== Rejected Features =====

N/A