mysqli::poll
Prepared statements are confusing in the beginning ..
mysqli->prepare() returns a so-called statement object which is used for subsequent operations eg execute, bind_param, store_result, bind_result, fetch, etc.
The statement object has private properties which update as each statement operation is carried out. I found these useful for understanding what is going on when writing a prepared statement function:
affected_rows
insert_id
num_rows
param_count
field_count
errno
error
sqlstate
id
But it took a little time to get my head around accessing them:
<?php
$stmt = $mysqli->prepare($query);
// .. $stmt-> operations ..
var_dump($stmt); // shows null values
var_dump($stmt->errno); // note literal, displays value
// .. $stmt-> operations ..
// to keep a copy ..
// get_object_properties() won't work
// clone() won't work
$properties = array();
foreach ($stmt as $name => $priv){
$properties[$name] = $stmt->$name; // works
// $properties[$name] = $priv; // won't work, foreach can't access private properties
}
$stmt->close();
// var_dump($stmt->errno) // won't work, $stmt is closed
?>
mysqli::prepare
mysqli_prepare
(PHP 5)
mysqli::prepare -- mysqli_prepare — Prépare une requête SQL pour l'exécution
Description
Style orienté objet
mysqli_stmt mysqli::prepare
( string $query
)
Style procédural
Prépare la requête SQL et retourne un gestionnaire de requête à utiliser pour les futures opérations sur la requête. La requête doit être composée d'une seule requête SQL.
Les paramètres de marques doivent être liés à des variables utilisées dans les fonctions mysqli_stmt_bind_param() et/ou mysqli_stmt_bind_result() avant d'exécuter la requête ou de récupérer les lignes.
Liste de paramètres
- link
-
Seulement en style procédural : Un identifiant de lien retourné par la fonction mysqli_connect() ou par la fonction mysqli_init()
- query
-
La requête, sous la forme d'une chaîne de caractères.
Note:
Vous ne devez pas ajouter de point virgule ou de \g dans la requête.
Ce paramètre peut inclure un ou plusieurs paramètres de marques dans la requête SQL avec le caractère "point d'interrogation" (?) à la position appropriée.
Note:
Les marques sont autorisées uniquement dans certaines endroits des requêtes SQL. Par exemple, elles le sont dans la liste VALUES() d'une requête INSERT (pour spécifier les valeurs des colonnes pour une ligne), ou dans une comparaison d'une clause WHERE pour spécifier une valeur de comparaison.
Cependant, elles ne sont pas autorisées pour les identifiants (comme les noms de tables ou de colonnes), dans une liste de sélection où les noms des colonnes doivent être retournés par une requête SELECT, ou pour spécifier un opérateur tel que le signe égal (=). La dernière restriction est nécessaire, car il est impossible de déterminer le type de paramètre. Il n'est pas non plus autorisé de comparer les marqueurs avec NULL par ? IS NULL. En général, les paramètres ne sont autorisés que dans les requêtes DML ("Data Manipulation Language") et non dans les requêtes DDL ("Data Definition Language").
Valeurs de retour
mysqli_prepare() retourne un objet de traitement ou FALSE si une erreur survient.
Exemples
Exemple #1 Exemple avec mysqli::prepare()
Style orienté objet
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* Vérification de la connexion */
if (mysqli_connect_errno()) {
printf("Échec de la connexion : %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* Crée une requête préparée */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
/* Lecture des marqueurs */
$stmt->bind_param("s", $city);
/* Exécution de la requête */
$stmt->execute();
/* Lecture des variables résultantes */
$stmt->bind_result($district);
/* Récupération des valeurs */
$stmt->fetch();
printf("%s est dans le district de %s\n", $city, $district);
/* Fermeture du traitement */
$stmt->close();
}
/* Fermeture de la connexion */
$mysqli->close();
?>
Style procédural
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* Vérification de la connexion */
if (mysqli_connect_errno()) {
printf("Échec de la connexion : %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* Crée une requête préparée */
if ($stmt = mysqli_prepare($link, "SELECT District FROM City WHERE Name=?")) {
/* Lecture des marqueurs */
mysqli_stmt_bind_param($stmt, "s", $city);
/* Exécution de la requête */
mysqli_stmt_execute($stmt);
/* Lecture des variables résultantes */
mysqli_stmt_bind_result($stmt, $district);
/* Récupération des valeurs */
mysqli_stmt_fetch($stmt);
printf("%s est dans le district de %s\n", $city, $district);
/* Fermeture du traitement */
mysqli_stmt_close($stmt);
}
/* Fermeture de la connexion */
mysqli_close($link);
?>
Les exemples ci-dessus vont afficher :
Amersfoort est dans le district de Utrecht
Voir aussi
- mysqli_stmt_execute() - Exécute une requête préparée
- mysqli_stmt_fetch() - Lit des résultats depuis une requête MySQL préparée dans des variables liées
- mysqli_stmt_bind_param() - Lie des variables à une requête MySQL
- mysqli_stmt_bind_result() - Lie des variables à un jeu de résultats
- mysqli_stmt_close() - Termine une requête préparée
add a note
User Contributed Notes
mysqli::prepare
Bernie van't Hof
01-Feb-2012 11:38
Darren
13-Jan-2012 04:09
I wrote this function for my personal use and figured I would share it. I am not sure if this is the appropriate forum but I wish I had this when I stumbled on to mysqli::prepare. The function is an update of the function I posted previously. The previous function could not handle multiple queries.
For queries:
Results of single queries are given as arrays[row#][associated Data Array]
Results of multiple queries are given as arrays[query#][row#][associated Data Array]
For queries which return an affected row#, affected rows are returned instead of (array[row#][associated Data Array])
Code and example are below:
<?php
function mysqli_prepared_query($link,$sql,$typeDef = FALSE,$params = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if(count($params) == count($params,1)){
$params = array($params);
$multiQuery = FALSE;
} else {
$multiQuery = TRUE;
}
if($typeDef){
$bindParams = array();
$bindParamsReferences = array();
$bindParams = array_pad($bindParams,(count($params,1)-count($params))/count($params),"");
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDef);
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param');
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences);
}
$result = array();
foreach($params as $queryKey => $query){
foreach($bindParams as $paramKey => $value){
$bindParams[$paramKey] = $query[$paramKey];
}
$queryResult = array();
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array();
$rowReferences = array();
while ($field = mysqli_fetch_field($resultMetaData)) {
$rowReferences[] = &$stmtRow[$field->name];
}
mysqli_free_result($resultMetaData);
$bindResultMethod = new ReflectionMethod('mysqli_stmt', 'bind_result');
$bindResultMethod->invokeArgs($stmt, $rowReferences);
while(mysqli_stmt_fetch($stmt)){
$row = array();
foreach($stmtRow as $key => $value){
$row[$key] = $value;
}
$queryResult[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$queryResult[] = mysqli_stmt_affected_rows($stmt);
}
} else {
$queryResult[] = FALSE;
}
$result[$queryKey] = $queryResult;
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
if($multiQuery){
return $result;
} else {
return $result[0];
}
}
?>
Example(s):
For a table of firstName and lastName:
John Smith
Mark Smith
Jack Johnson
Bob Johnson
<?php
//single query, single result
$query = "SELECT * FROM names WHERE firstName=? AND lastName=?";
$params = array("Bob","Johnson");
mysqli_prepared_query($link,$query,"ss",$params)
/*
returns array(
0=> array('firstName' => 'Bob', 'lastName' => 'Johnson')
)
*/
//single query, multiple results
$query = "SELECT * FROM names WHERE lastName=?";
$params = array("Smith");
mysqli_prepared_query($link,$query,"s",$params)
/*
returns array(
0=> array('firstName' => 'John', 'lastName' => 'Smith')
1=> array('firstName' => 'Mark', 'lastName' => 'Smith')
)
*/
//multiple query, multiple results
$query = "SELECT * FROM names WHERE lastName=?";
$params = array(array("Smith"),array("Johnson"));
mysqli_prepared_query($link,$query,"s",$params)
/*
returns array(
0=>
array(
0=> array('firstName' => 'John', 'lastName' => 'Smith')
1=> array('firstName' => 'Mark', 'lastName' => 'Smith')
)
1=>
array(
0=> array('firstName' => 'Jack', 'lastName' => 'Johnson')
1=> array('firstName' => 'Bob', 'lastName' => 'Johnson')
)
)
*/
?>
Hope it helps =)
Darren
10-Jan-2012 08:59
For those learning mysqli::prepare and mysqli_stmt::bind_params for the first time, here is a commented block of code which executes prepared queries and returns data in a similar format to the return values of mysqli_query. I tried to minimize unnecessary classes, objects, or overhead for two reasons:
1) facilitate learning
2) allow relativity interchangeable use with mysqli_query
My goal is to lower the learning curve for whoever is starting out with these family of functions. I am by no means an expert coder/scripter, so I am sure there are improvements and perhaps a few bugs, but I hope not =)
<?php
/*
Function: mysqli_prepared_query()
Executes prepared querys given query syntax, and bind parameters
Returns data in array format
Arguments:
mysqli_link
mysqli_prepare query
mysqli_stmt_bind_param argmuent list in the form array($typeDefinitinonString, $var1 [, mixed $... ])
Return values:
When given SELECT, SHOW, DESCRIBE or EXPLAIN statements: returns table data in the form resultArray[row number][associated field name]
Returns number of rows affacted when given other queries
Returns FALSE on error
*/
function mysqli_prepared_query($link,$sql,$bindParams = FALSE){
if($stmt = mysqli_prepare($link,$sql)){
if ($bindParams){
$bindParamsMethod = new ReflectionMethod('mysqli_stmt', 'bind_param'); //allows for call to mysqli_stmt->bind_param using variable argument list
$bindParamsReferences = array(); //will act as arguments list for mysqli_stmt->bind_param
$typeDefinitionString = array_shift($bindParams);
foreach($bindParams as $key => $value){
$bindParamsReferences[$key] = &$bindParams[$key];
}
array_unshift($bindParamsReferences,$typeDefinitionString); //returns typeDefinition as the first element of the string
$bindParamsMethod->invokeArgs($stmt,$bindParamsReferences); //calls mysqli_stmt->bind_param suing $bindParamsRereferences as the argument list
}
if(mysqli_stmt_execute($stmt)){
$resultMetaData = mysqli_stmt_result_metadata($stmt);
if($resultMetaData){
$stmtRow = array(); //this will be a result row returned from mysqli_stmt_fetch($stmt)
$rowReferences = array(); //this will reference $stmtRow and be passed to mysqli_bind_results
while ($field = mysqli_fetch_field($resultMetaData)) {
$rowReferences[] = &$stmtRow[$field->name];
}
mysqli_free_result($resultMetaData);
$bindResultMethod = new ReflectionMethod('mysqli_stmt', 'bind_result');
$bindResultMethod->invokeArgs($stmt, $rowReferences); //calls mysqli_stmt_bind_result($stmt,[$rowReferences]) using object-oriented style
$result = array();
while(mysqli_stmt_fetch($stmt)){
foreach($stmtRow as $key => $value){ //variables must be assigned by value, so $result[] = $stmtRow does not work (not really sure why, something with referencing in $stmtRow)
$row[$key] = $value;
}
$result[] = $row;
}
mysqli_stmt_free_result($stmt);
} else {
$result = mysqli_stmt_affected_rows($stmt);
}
mysqli_stmt_close($stmt);
} else {
$result = FALSE;
}
} else {
$result = FALSE;
}
return $result;
}
?>
Here's hoping the PHP gods don't smite me.
Zeebuck
24-Jul-2011 06:51
I think that the purpose that it was originally built for, and the purpose that people use it for today, have diverged. But why dwell on the original purpose? Obviously more code has been put into prepared statements today to allow it to be used to prevent sql injections, so it is now part of the design purpose today, as well as performance on repeatable statements.
Codeguy
29-Apr-2011 01:39
The actual purpose to use a prepared statement in sql is to cut the cost of processing queries; NOT to separate data from query. That's how it's being used w/ php NOW, not how it was designed to be used in the first place. With SQL you cut the cost of executing multiple similar queries down by using a prepared statement.. Doing so cuts out the parsing, validation and most often generates an execution plan for said query up front. Which is why they run faster in a loop, than their IMMEDIATE Query cousins do. Do not assume that just because someone uses php and this function this way does not mean that it is THE way, or only way to do it. Although it is more secure than general queries but they are also more limited in what they can do or more precisely how you can go about doing it.
prwexler at earthlink dot net
08-Apr-2011 10:14
Actually, the purpose of a prepare statement is to separate user input from SQL commands. This increases security by preventing a user from executing his own SQL code and damaging the integrity of a database.
Domenic Denicola
10-Jan-2010 12:18
It's worth noting that this function will error out with "Number of variables doesn't match number of parameters in prepared statement" for reasons that have nothing to do with the number of variables and parameters.
For example, I made the newbie mistake of saying "SELECT (username, password) FROM mytable WHERE something = ?" instead of "SELECT username, password FROM mytable WHERE something = ?" and got that error.
So basically, if you get that error, check your query syntax very carefully before poking around for the various problems that could occur with binding, pass-by-reference, etc.
sdepouw at NOSPAM dot com
29-Jul-2009 12:03
I don't know how obvious this was for anyone else, but if you attempt to prepare a query for a table that doesn't exist in the database the connection currently points to (or if your query is invalid in some other way, I suppose), an object will not be returned. I only noticed this after doing some digging when I kept getting a fatal error saying that my statement variable was not an set to an instance of an object (it was probably null).
Replace NOSPAM with nimblepros to e-mail me.
rafael at stiod dot com
29-Sep-2008 10:44
All data must be fetched before a new statement prepare
admin at xorath dot com
15-Aug-2007 04:39
Performance note to those who wonder. I performed a test where first of all inserted about 30,000 posts with one PK:id and a varchar(20), where the varchar data was md5-hash for the current iterator value just to fill with some data.
The test was performed on a dedicated ubuntu 7.04 server with apache2/php5/mysql5.0 running on Athlon 64 - 3000+ with 512MB of RAM. The queries where tested with a for-loop from 0 to 30000 first with:
<?php
for ( $i = 0; $i <= 30000; ++$i )
{
$result = $mysqli->query("SELECT * FROM test WHERE id = $i");
$row = $result->fetch_row();
echo $row[0]; //prints id
}
?>
which gave a page-load time of about 3.3seconds avarage, then with this loop:
<?php
$stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?");
for ( $i = 0; $i <= 30000; ++$i )
{
$stmt->bind_param("i", $i);
$stmt->execute();
$stmt->bind_result($id, $md5);
$stmt->fetch();
echo $id;
}
$stmt->close();
?>
and the avarage page-load was lowered by 1.3sec, which means about 2.0 sec avarage! Guess the performance difference could be even greater on a more complex/larger table and more complex SQL-queries.
nom0ny at yahoo dot com
25-May-2007 06:30
It must be noted in the Description whether developers should call mysqli_stmt_close prior to executing mysqli_prepare again on the same statement variable.
Example, Script A calls mysqli_stmt_close twice:
<?php
/* Script A -- We are already connected to the database */
$stmt = mysqli_prepare($link, "INSERT INTO table VALUES (?, ?, 100)"); /* Query 1 */
mysqli_stmt_bind_param($stmt, "si", $string, $integer);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt); // CLOSE $stmt
$stmt = mysqli_prepare($link, "INSERT INTO table VALUES ('PHP', ?, ?)"); /* Query 2 */
mysqli_stmt_bind_param($stmt, "ii", $integer, $code);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt); // CLOSE $stmt
/* Script A -- Continues on... */
?>
Next, we have Script B, calling mysqli_prepare again before issuing mysqli_stmt_close on the prior statement.
<?php
/* Script B -- We are already connected to the database */
$stmt = mysqli_prepare($link, "INSERT INTO table VALUES (?, ?, 100)"); /* Query 1 */
mysqli_stmt_bind_param($stmt, "si", $string, $integer);
mysqli_stmt_execute($stmt);
$stmt = mysqli_prepare($link, "INSERT INTO table VALUES ('PHP', ?, ?)"); /* Query 2 */
mysqli_stmt_bind_param($stmt, "ii", $integer, $code);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt); // CLOSE $stmt
/* Script B -- Continues on... */
?>
Which method is more efficient and should be used by developers?
Adam
10-Aug-2006 09:03
The purpose of prepared statements is to not include data in your SQL statements. Including them in your SQL statements is NOT safe. Always use prepared statements. They are cleaner to use (code easier to read) and not prone to SQL injections.
Escaping strings to include in SQL statements doesn't work very well in some locales hence it is not safe.
codeFiend <aeontech at gmail dot com>
05-May-2006 08:47
Note that single-quotes around the parameter markers _will_ prevent your statement from being prepared correctly.
Ex:
<?php
$stmt = $mysqli->prepare("INSERT INTO City (District) VALUES ('?')");
echo $stmt->param_count." parameters\n";
?>
will print 0 and fail with "Number of variables doesn't match number of parameters in prepared statement" warning when you try to bind the variables to it.
But
<?php
$stmt = $mysqli->prepare("INSERT INTO City (District) VALUES (?)");
echo $stmt->param_count." parameters\n";
?>
will print 1 and function correctly.
Very annoying, took me an hour to figure this out.
Ulf Wostner
23-Jan-2006 01:53
Here is an example using bind_param and bind_result, showing iteration over a list of cities.
Note that there's some bug-potential in cases where the query returns NULL for some parameter value,
but the bind_result variables still might be bound. So, we use a conditional to spray the spot first.
$mysqli->select_db("world");
$template = "SELECT District, CountryCode FROM City WHERE Name=?";
printf("Prepare statement from template: %s\n", $template);
$cities = array('San Francisco', 'Lisbon', 'Lisboa', 'Marrakech', 'Madrid');
printf("Cities: %s\n", join(':', $cities));
if ($stmt = $mysqli->prepare($template)) {
foreach($cities as $city) {
// bind the string $city to the '?'
$stmt->bind_param("s", $city);
$stmt->execute();
// bind result variables
$stmt->bind_result($d,$cc);
// 'Lisbon' is not found in the world.City table, but 'Lisboa' is.
// Using a conditional we avoid putting Lisbon in California.
if($stmt->fetch()) {
printf("%s is in %s, %s\n", $city, $d, $cc);
}
}
$stmt->close();
}
With the conditional statement we get the desired result:
Prepare statement from template: SELECT District,CountryCode FROM City WHERE Name=?
Cities: San Francisco:Lisbon:Lisboa:Marrakech:Madrid
San Francisco is in California, USA
Lisboa is in Lisboa, PRT
Marrakech is in Marrakech-Tensift-Al, MAR
Madrid is in Madrid, ESP
But, without the conditional statement we would put Lisbon in California:
San Francisco is in California, USA
Lisbon is in California, USA
Lisboa is in Lisboa, PRT
Marrakech is in Marrakech-Tensift-Al, MAR
Madrid is in Madrid, ESP
David Kramer
20-Dec-2005 05:50
I don't think these are good examples, because the primary use of prepared queries is when you are going to call the same query in a loop, plugging in different values each time. For instance, if you were generating a report and needed to run the same query for each line, tweaking the values in the WHERE clause, or importing data from another system.