mysqli_prepare

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.

范例

例子 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

j dot andrew dot mcentire at gmail dot com
15-Aug-2006 11: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
15-Aug-2006 04: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
11-Aug-2006 06: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 05: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>
06-May-2006 05: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
24-Jan-2006 12: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
21-Dec-2005 04: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_prepare

Description

返回值

参见

范例