MENU


PHP Prepared Statements

mysqli::prepare($s) or mysqli_prepare($l, $s) prepares a single query $s and returns a mysqli_stmt prepared statement object.
mysqli::stmt_init() or mysqli_stmt_init($l) initializes a prepared statement appropriate for mysqli_stmt_prepare().

The following functions in this sub-section (5.8.4) belong to the mysqli_stmt class, which represents a prepared object. Only the object-oriented forms are desribed. The procedural form of each function for a prepared statement object can be obtained by prepending the function name with mysqli_stmt_, and using the mysqli_stmt object as the first parameter. For example, the procedural form for PS->prepare($s) is mysqli_stmt_prepare ($stmt,$s). Unless otherwise specified, these functions return true on SUCCESS or FALSE on failure. 

<!DOCTYPE html><html><head></head><body><pre>
<?php

// ****** 1. Database Connection
$S=mysqli_connect("localhost","root","password","testDB");


// ****** 2. Table Definition
$S->query("DROP TABLE IF EXISTS tbl");
$S->query("
CREATE TABLE tbl(
   a INT,
   b DOUBLE,
   c VARCHAR(10),
   d BLOB
)");


// ****** 3. Data Management
$PS = $S->prepare("INSERT INTO tbl VALUES (?,?,?,?)");
$PS->bind_param("idsb",$i,$d,$s,$b);// i for integer
                                                        // d for double
                                                        // s for string
                                                        // b for blob
$i = 100;
$d = 3.14;
$s = "Hello";
$b = "Some long long long long text";
$PS->execute();  // inserts the first row
$PS->execute();  // inserts the second row
$PS->execute();  // inserts the third row


// ****** 4. Data Retrieval
$PS->prepare("SELECT a,b FROM tbl");

$PS->execute();
$PS->bind_result($i,$d);
while ($PS->fetch()){
   echo "$i,$d\n";
}

$PS->execute();
tabulate($PS->get_result());

$PS->execute();
$PS->store_result(); // needed by data_seek()
$PS->data_seek(1);
while ($PS->fetch()){
   echo "$i,$d\n";
}

echo "\n";
echo "num_rows:".$PS->num_rows."\n";
echo "affected_rows:".$PS->affected_rows."\n";
echo "field_count:".$PS->field_count."\n";
echo "param_count:".$PS->param_count."\n";
echo "insert_id:".$PS->insert_id."\n";
echo "sqlstate:".$PS->sqlstate."\n";
echo "errno:".$PS->errno."\n";
echo "error:".$PS->error."\n";
echo "error_list:"; print_r($PS->error_list); echo "\n";
echo "get_warnings():"; print_r($PS->get_warnings()); echo "\n";

$PS->free_result();
//$PS->reset();  // resets the statement
$PS->close();    // frees the memory


// ****** A useful, general-purpose result printer
function tabulate($result){
   $fInfo = $result->fetch_fields();
   echo "<table border='1'><tr>";
   foreach($fInfo as $col){
      echo "<td>".$col->name."</td>";
   }
   while ($row = $result->fetch_row()){
      echo "</tr><tr>";
      foreach ($row as $val){
	     echo "<td>".$val."</td>";
	  }
   }
   echo "</tr></table><br/>";
}

?>
</pre></body></html>

100,3.14

100,3.14

100,3.14



  

    a
    b
  

  

    100
    3.14
  

  

    100
    3.14
  

  

    100
    3.14
  






  100,3.14

  100,3.14

  

num_row:3
affected_rows:3
field_count:2
param_count:0
insert_id:0
sqlstate:00000
errono:0
error:
error_list:Array
{
}
get_warning();