|
|
 |
mysqli_prepare (PHP 5) mysqli_prepare (no version information, might be only in CVS) mysqli->prepare --
Prepare a SQL statement for execution
DescriptionProcedure style: mysqli_stmt mysqli_prepare ( mysqli link, string query ) Object oriented style (method) class mysqli { mysqli_stmt prepare ( string query ) }
mysqli_prepare() prepares the SQL query pointed to by the
null-terminated string query, and returns a statement handle to be used for
further operations on the statement. The query must consist of a single SQL statement.
Замечание:
You should not add a terminating semicolon or \g
to the statement.
The parameter query can include one or more parameter markers
in the SQL statement by embedding question mark (?) characters
at the appropriate positions.
Замечание:
The markers are legal only in certain places in SQL statements.
For example, they are allowed in the VALUES() list of an INSERT statement
(to specify column values for a row), or in a comparison with a column in
a WHERE clause to specify a comparison value.
However, they are not allowed for identifiers (such as table or column names),
in the select list that names the columns to be returned by a SELECT statement,
or to specify both operands of a binary operator such as the =
equal sign. The latter restriction is necessary because it would be impossible
to determine the parameter type. It's not allowed to compare marker
with NULL by ? IS NULL too.
In general, parameters are legal only in Data
Manipulation Languange (DML) statements, and not in Data Defination Language
(DDL) statements.
The parameter markers must be bound to application variables using
mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result()
before executing the statement or fetching rows.
Возвращаемые значения
mysqli_prepare() returns a statement object or FALSE if an error occured.
ПримерыПример 1. Object oriented style |
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
$stmt->bind_param("s", $city);
$stmt->execute();
$stmt->bind_result($district);
$stmt->fetch();
printf("%s is in district %s\n", $city, $district);
$stmt->close();
}
$mysqli->close();
?>
|
|
Пример 2. Procedural style |
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
if ($stmt = mysqli_prepare($link, "SELECT District FROM City WHERE Name=?")) {
mysqli_stmt_bind_param($stmt, "s", $city);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $district);
mysqli_stmt_fetch($stmt);
printf("%s is in district %s\n", $city, $district);
mysqli_stmt_close($stmt);
}
mysqli_close($link);
?>
|
|
Результат выполнения данного примера: Amersfoort is in district Utrecht |
mysqli_prepare
Mikael
24-May-2006 02:00
That's not exactly true. The prepared statement is stored in the database, and is therefor faster to execute next time. This means that the second time a page is rendered, it's not eating as much database CPU. Even in a different page, if it's the exact same query string. Ie, even the "prepare" call is faster the second time, since the database can see that it already did that prepare once.
codeFiend <aeontech at gmail dot com>
05-May-2006 02: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 08: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 12: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.
| |
mysqli_ping