rfc:mysqli_execute_query

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
rfc:mysqli_execute_query [2022/04/24 13:35] – Example of old/unsafe escaping (thanks Guilliam) craigfrancisrfc:mysqli_execute_query [2022/05/25 19:18] – close vote craigfrancis
Line 1: Line 1:
 ====== PHP RFC: MySQLi Execute Query ====== ====== PHP RFC: MySQLi Execute Query ======
  
-  * Version: 0.1 +  * Version: 1
-  * Voting Start: ? +
-  * Voting End: ?+
   * RFC Started: 2022-04-21   * RFC Started: 2022-04-21
-  * RFC Updated: 2022-04-21+  * RFC Updated: 2022-05-11 
 +  * Voting Start: 2022-05-11 15:00 UTC / 16:00 BST 
 +  * Voting End: 2022-05-25 15:00 UTC / 16:00 BST
   * Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]   * Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]
-  * Status: Under Discussion+  * Status: Accepted
   * First Published at: https://wiki.php.net/rfc/mysqli_execute_query   * First Published at: https://wiki.php.net/rfc/mysqli_execute_query
   * GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc   * GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc
Line 32: Line 32:
 <code php> <code php>
 $db = new mysqli('localhost', 'user', 'password', 'database'); $db = new mysqli('localhost', 'user', 'password', 'database');
- 
-$sql = 'SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)'; 
  
 $name = '%a%'; $name = '%a%';
Line 51: Line 49:
  
 <code php> <code php>
-$statement = $db->prepare($sql); +$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)'); 
-$statement->bind_param('sss', $name, $type1, $type2);+$statement->bind_param('sii', $name, $type1, $type2);
 $statement->execute(); $statement->execute();
  
Line 63: Line 61:
  
 <code php> <code php>
-$statement = $db->prepare($sql);+$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)');
 $statement->execute([$name, $type1, $type2]); $statement->execute([$name, $type1, $type2]);
  
Line 74: Line 72:
  
 <code php> <code php>
-foreach ($db->execute_query($sql, [$name, $type1, $type2]) as $row) {+foreach ($db->execute_query('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)', [$name, $type1, $type2]) as $row) {
     print_r($row);     print_r($row);
 } }
Line 108: Line 106:
 ==== Returning false ==== ==== Returning false ====
  
-Because the implementation is effectively calling [[https://www.php.net/mysqli_stmt_get_result|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 [[https://wiki.php.net/rfc/mysqli_default_errmode|Change Default mysqli Error Mode RFC]] was accepted, and Exceptions are used by default.+The implementation is effectively calling [[https://www.php.net/mysqli_stmt_get_result|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 [[https://wiki.php.net/rfc/mysqli_default_errmode|Change Default mysqli Error Mode RFC]] was accepted, and Exceptions are used by default. 
 + 
 +==== Properties ==== 
 + 
 +Because [[https://www.php.net/manual/en/class.mysqli-stmt.php|mysqli_stmt]] is not returned, it's not possible to use its properties directly: 
 + 
 +  - int|string **$affected_rows** - use //$mysqli->affected_rows// or //mysqli_affected_rows($mysqli)// 
 +  - int|string **$insert_id** - use //$mysqli->insert_id// or //mysqli_insert_id($mysqli)// 
 +  - int|string **$num_rows** - also available on //mysqli_result// 
 +  - int **$param_count** 
 +  - int **$field_count** - also available on //mysqli_result// 
 +  - int **$errno** - use //mysqli_errno($mysqli)//, //$mysqli->errno// 
 +  - string **$error** - use //mysqli_error($mysqli)//, //$mysqli->error// 
 +  - array **$error_list** - use //mysqli_error_list($mysqli)//, //$mysqli->error_list// 
 +  - string **$sqlstate** - use //mysqli_sqlstate($mysqli)//, //$mysqli->sqlstate// 
 +  - int **$id** 
 + 
 +It's also worth noting the error property usage will hopefully reduce, as more developers use //mysqli_sql_exception// for errors (because the mysqli Error Mode now defaults to //MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT//).
  
 ==== Re-using Statements ==== ==== Re-using Statements ====
  
-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()//.+The implementation discards the //mysqli_stmt// object immediately, so you cannot re-issue a statement with new parameters. Anyone who would benefit from this (to skip running prepare again), can still use //mysqli_prepare()//.
  
 ==== Updating Existing Functions ==== ==== Updating Existing Functions ====
  
-Cannot change //mysqli_query()// because it's second argument is //$resultmode//.+Cannot change //mysqli_query()// because its 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. Cannot replace the deprecated //mysqli_execute()// function, which is an alias for //mysqli_stmt_execute()//, because it would create a backwards compatibility issue.
Line 156: Line 171:
 ===== Open Issues ===== ===== Open Issues =====
  
-==== Affected Rows ==== +None
- +
-Currently //$mysqli->affected_rows// and //mysqli_affected_rows($mysqli)// returns -1. +
- +
-==== Properties ==== +
- +
-Because [[https://www.php.net/manual/en/class.mysqli-stmt.php|mysqli_stmt]] is not returned, it's not possible to use its properties: +
- +
-  - int|string **$affected_rows** - see above +
-  - int|string **$insert_id** - can use //$mysqli->insert_id// or //mysqli_insert_id($mysqli)// +
-  - int|string **$num_rows** - also available on //mysqli_result// +
-  - int **$param_count** +
-  - int **$field_count** - also available on //mysqli_result// +
-  - int **$errno** - can use //mysqli_errno($mysqli)//, //$mysqli->errno// +
-  - string **$error** - can use //mysqli_error($mysqli)//, //$mysqli->error// +
-  - array **$error_list** - can use //mysqli_error_list($mysqli)//, //$mysqli->error_list// +
-  - string **$sqlstate** - can use //mysqli_sqlstate($mysqli)//, //$mysqli->sqlstate// +
-  - int **$id**+
  
 ===== Unaffected PHP Functionality ===== ===== Unaffected PHP Functionality =====
Line 187: Line 185:
 Accept the RFC Accept the RFC
  
-TODO+<doodle title="mysqli_execute_query" auth="craigfrancis" voteType="single" closed="true"> 
 +   * Yes 
 +   * No 
 +</doodle>
  
 ===== Implementation ===== ===== Implementation =====
  
 [[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|From Kamil Tekiela]] (proof of concept) [[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|From Kamil Tekiela]] (proof of concept)
 +
 +This implementation copies some details to the mysqli object, but not the affected rows. This means //mysqli_affected_rows($mysqli)// and //$mysqli->affected_rows// will currently return -1.
  
 ===== References ===== ===== References =====
Line 199: Line 202:
 ===== Rejected Features ===== ===== Rejected Features =====
  
-TODO+None
  
rfc/mysqli_execute_query.txt · Last modified: 2022/08/04 13:28 by dharman

Page Tools