Web студия "GrandView"

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
mysqli_prepare

joe
07-Sep-2007 01:10


in response to J dot andrew's   type function....



   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.



why do a string comparison?



you could do something like



            if is_int($QueryVar)

                $QueryVarType = "i";

            elseif is_string($QueryVar)

                $QueryVarType = "s";

            elseif is_double($QueryVar)

                $QueryVarType = "d";

            else

                die ("Invalid non-scalar value")

admin at xorath dot com
15-Aug-2007 01:32


Sorry for the typo it was performed on mysql 5.0 ofcourse.

admin at xorath dot com
15-Aug-2007 10: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/mysql2.9 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 12: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 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.

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_prepare

Description

Возвращаемые значения

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

Примеры