rfc:mysqli_execute_parameters

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
rfc:mysqli_execute_parameters [2020/12/26 11:16] – Add note about mysqli::query() craigfrancisrfc:mysqli_execute_parameters [2020/12/26 14:24] (current) – Added syntax highlighting dharman
Line 15: Line 15:
 Using parameterised queries with //mysqli// is too complicated: Using parameterised queries with //mysqli// is too complicated:
  
-    $db = new mysqli('localhost', 'test', 'test', 'test');+<code php> 
 +$db = new mysqli('localhost', 'test', 'test', 'test');
          
-    $sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?'; +$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?'; 
-     + 
-    $name = '%a%'; +$name = '%a%'; 
-    $type = 'admin'; +$type = 'admin'; 
-     + 
-    $statement = $db->prepare($sql); +$statement = $db->prepare($sql); 
-    $statement->bind_param('ss', $name, $type); +$statement->bind_param('ss', $name, $type); 
-    $statement->execute(); +$statement->execute(); 
-     + 
-    $result = $statement->get_result(); +$result = $statement->get_result(); 
-     + 
-    while ($row = $result->fetch_assoc()) { +while ($row = $result->fetch_assoc()) { 
-      print_r($row); +    print_r($row); 
-    }+} 
 +</code>
  
 Note that variables need to be used in //bind_param()//, because values cannot be passed by reference. Note that variables need to be used in //bind_param()//, because values cannot be passed by reference.
Line 42: Line 44:
 Introduce //mysqli::execute//() (and replace the deprecated //mysqli_execute()// function) to make parameterised queries much easier, e.g. 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 = ?'; +<code php> 
-     +$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?'; 
-    $parameters = ['%a%', 'admin']; +   
-     +$parameters = ['%a%', 'admin']; 
-    $result = $db->execute($sql, $parameters); + 
-     +$result = $db->execute($sql, $parameters); 
-    while ($row = $result->fetch_assoc()) { + 
-      print_r($row); +while ($row = $result->fetch_assoc()) { 
-    }+    print_r($row); 
 +} 
 +</code>
  
 We probably cannot change //mysqli::query()// because it's second argument is //$resultmode//. We probably cannot change //mysqli::query()// because it's second argument is //$resultmode//.
Line 58: Line 62:
 Something like this: Something like this:
  
-    class mysqli_rfc extends mysqli { +<code php> 
-     +<?php 
-      function execute($sql, $parameters = []) { + 
-    +class mysqli_rfc extends mysqli { 
 +    function execute($sql, $parameters = []) {
         $statement = mysqli_prepare($this, $sql);         $statement = mysqli_prepare($this, $sql);
-    +
         $ref_types = '';         $ref_types = '';
         foreach ($parameters as $key => $value) {         foreach ($parameters as $key => $value) {
-          $ref_types .= (is_int($value) ? 'i' : 's'); // 'd' for double, or 'b' for blob. +            $ref_types .= (is_int($value) ? 'i' : 's'); // 'd' for double, or 'b' for blob. 
-          $ref_values[] = &$parameters[$key];+            $ref_values[] = &$parameters[$key];
         }         }
-    +
         array_unshift($ref_values, $ref_types);         array_unshift($ref_values, $ref_types);
-    +
         call_user_func_array([$statement, 'bind_param'], $ref_values);         call_user_func_array([$statement, 'bind_param'], $ref_values);
-    +
         $statement->execute();         $statement->execute();
-    +
         return $statement->get_result();         return $statement->get_result();
-     
-      } 
-     
     }     }
 +}
 +</code>
  
 Where //$sql// should be a [[https://wiki.php.net/rfc/is_literal|safe literal string]]. Where //$sql// should be a [[https://wiki.php.net/rfc/is_literal|safe literal string]].
rfc/mysqli_execute_parameters.1608981373.txt.gz · Last modified: 2020/12/26 11:16 by craigfrancis

Page Tools