PHP: mysqli_prepare - Manual

downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | conferences | my php.net

search for in the

mysqli_query mysqli_ping

Last updated: Sat, 27 Jan 2007

view this page in

# mysqli_prepare

(PHP 5)

mysqli_prepare

(no version information, might be only in CVS)

mysqli->prepare -- Prepare a SQL statement for execution

## Description

Procedure 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.

## Смотрите также

mysqli_stmt_execute(), mysqli_stmt_fetch(), mysqli_stmt_bind_param(), mysqli_stmt_bind_result() и mysqli_stmt_close().

## Примеры

Пример 1. Object oriented style

`<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
/* bind parameters for markers */
$stmt->bind_param("s", $city);
/* execute query */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($district);
/* fetch value */
$stmt->fetch();
printf("%s is in district %s\n", $city, $district);
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>`

Пример 2. Procedural style

`<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* create a prepared statement */
if ($stmt = mysqli_prepare($link, "SELECT District FROM City WHERE Name=?")) {
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $city);
/* execute query */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $district);
/* fetch value */
mysqli_stmt_fetch($stmt);
printf("%s is in district %s\n", $city, $district);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>`

Результат выполнения данного примера:

    Amersfoort is in district Utrecht

add a note User Contributed Notes
mysqli_prepare
j dot andrew dot mcentire at gmail dot com
14-Aug-2006 08:37
`Advancing on my previous note to modify the mysqli_stmt and mysqli classes; I've coded the following to allow the passing of an array rather than a list of variables to mysqli_stmt_bind_param. As written, it is for my DB class created specifically for inserts, but is extensible to accomidate various uses. For the dynamic quality of my website, I wanted a function that would allow me to fetch and insert variable numbers of values from and into a table:
<?php
class PS extends mysqli_stmt { };
class DB extends mysqli {
public function type ( $var ) {
switch ( gettype( $var ) ) {
case 'integer':
return 'i';
break;
case 'double':
return 'd';
break;
case 'string':
return 's';
break;
case 'boolean':
case 'array':
case 'object':
case 'resource':
case 'NULL':
default:
return NULL;
break;
} // could re-write to return first character, or remove breaks. Meh.
public function arr_to_arg ( $array ) {
$keys = $vals = $type = $args = NULL;
foreach ( $array as $key => $val ) {
$keys .= $key . ", ";
$vals .= "?, ";
$type .= $this->type( $vals );
$args .= '$array[\'' . $key . '\'], ';
}
$keys = rtrim( $keys, ", " );
$vals = rtrim( $vals, ", " );
$type = "'" . $type . "', ";
$args = $type . rtrim( $args, ", " );
return array( $keys, $vals, $args );
}
public function prepared_insert ( $table, $array ) {
list( $keys, $vals, $args ) = $this->arr_to_arg( $array );
$query = "INSERT INTO ${table} (${keys}) VALUES (${vals})";
$PS = new PS( $this, $query );
$line = "\$PS->bind_param( $args );";
eval ( $line );
$PS->execute( );
$PS->close( );
}
}
?>`
j dot andrew dot mcentire at gmail dot com
14-Aug-2006 01:38
`After a little searching, I've implemented the following method to extend the mysqli class when using prepared statements. Please forgive the simplicity of the code:
<?php
class DB extends mysqli {
public $host = 'localhost';
public $user = 'username';
public $pass = 'password';
public $dbse = 'database';
public function __construct ( ) {
parent::__construct(
$this->host,
$this->user,
$this->pass,
$this->dbse );
}
public function prepare ( $query = '' ) {
return new PS( $this, $query );
}
}
class PS extends mysqli_stmt {
public function __construct ( $mysqli, $query ) {
parent::__construct( $mysqli, $query );
}
}`
Adam
10-Aug-2006 03: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.`
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.`
add a note
mysqli_query mysqli_ping

Last updated: Sat, 27 Jan 2007
show source | credits | stats | sitemap | contact | advertising | mirror sites
Copyright © 2001-2007 The PHP Group
All rights reserved.
This mirror generously provided by: Yahoo! Inc.
Last updated: Sat Mar 10 10:36:09 2007 PST