PDO preprocessing statement Pdostatement Object Usage Summary

PDO preprocessing statement Pdostatement Object Usage Summary

PDO support for preprocessing statements requires the use of Pdostatement class objects, but they are not instantiated through the new keyword, but are returned directly after a preprocessed SQL statement is prepared in the database server by the prepare () method in the PDO object. If the Pdostatement class object returned before the query () method in the PDO object is executed, it represents only a result set object. If the Pdostatement class object generated by the prepare () method in the PDO object is executed, it is a query object that can define and execute parameterized SQL commands. All member methods in the Pdostatement class are as follows:

Pdostatement::bindcolumn― bind a column to a PHP variable

pdostatement::bindparam― bind a parameter to the specified variable name

pdostatement::bindvalue― Binds a value to a parameter

pdostatement::closecursor― closes the cursor so that the statement can be executed again.

Pdostatement::columncount― Returns the number

of columns in the result set Pdostatement::d ebugdumpparams― print a SQL preprocessing command

pdostatement::errorcode― get the SQLSTATE

associated with the previous statement handle operation Pdostatement::errorinfo― gets the extended error message associated with the previous statement handle operation

Pdostatement::execute― executes a preprocessing statement

pdostatement::fetch― Gets the next row from the result set

pdostatement::fetchall― returns an array containing all the rows in the result set

pdostatement::fetchcolumn― returns a separate column from the next row in the result set.

Pdostatement::fetchobject― Gets the next row and returns as an object.

pdostatement::getattribute― Retrieves a statement property

Pdostatement::getcolumnmeta― returns the metadata for one column in the result set

pdostatement::nextrowset― in a multiline set statement handle to the next rowset

pdostatement:: Rowcount― returns the number of rows affected by the previous SQL statement

pdostatement::setattribute― Set a statement property

pdostatement::setfetchmode― Sets the default fetch mode for the statement.

1, prepare the statement

Executing a SQL query repeatedly, using different parameters for each iteration, is the most efficient use of a preprocessing statement. With preprocessing statements, you first need to have a SQL statement ready in the database server, but you do not need to execute it immediately. PDO supports the use of placeholder syntax to bind variables to this preprocessed SQL statement. For a prepared SQL statement, if you want to change some column values every time you execute it, you must use the placeholder instead of the specific column value. There are two types of syntax for using placeholders in PDO: named arguments and question mark arguments, and which syntax depends on personal preferences.

Inserts a statement using a named argument as a placeholder:

$DBH->prepare ("INSERT into ContactInfo (Name,address,phone) VALUES (: Name,:address,:p Hone)");

You need to customize a string as a named parameter, each named parameter needs a colon (:) to start, and the name of the parameter must be meaningful, preferably the same as the corresponding field name.

Use a question mark (?) Inserts the argument as a placeholder INSERT statement:

$DBH->prepare ("INSERT into ContactInfo (Name,address,phone) VALUES (?,?,?)");

The question mark argument must correspond to the position order of the field. Regardless of which parameter is used as a placeholder query, or if a placeholder is not used in a statement, you need to use the prepare () method in the PDO object to prepare the query that will be used for the iteration and return the Pdostatement class object.
2. Binding Parameters

When the SQL statement is ready on the database server by using the prepare () method in the PDO object, if you use a placeholder, you need to replace the input parameters each time you execute. You can bind a parameter variable to a prepared placeholder (the position or name corresponds) by pdostatement the Bindparam () method in the object. The prototype of the method Bindparame () looks like this:

BOOL Pdostatement::bindparam (mixed $parameter, mixed & $variable [, int $data _type = PDO::P aram_str [, int $length [ , mixed $driver _options]])

The first parameter, parameter, is required if the placeholder syntax in the prepared query uses the Name argument, then the Name argument string is provided as the first argument of the Bindparam () method. If the placeholder syntax uses the question mark argument, the index offset of the column-value placeholder in the prepared query is used as the first parameter of the method.
The second parameter, variable, is also optional, providing a value that supplies the placeholder specified by the first argument. Because the parameter is passed by reference, only variables can be supplied as arguments and no values can be supplied directly.

The third parameter, data_type, is an option to set the data type for the currently bound parameter. Can be the following values.

PDO::P Aram_bool Represents a Boolean data type.

PDO::P aram_null represents the NULL type in SQL.

PDO::P aram_int represents the integer data type in SQL.

PDO::P Aram_str represents the char, varchar, and other string data types in SQL.

PDO::P Aram_lob represents a large object data type in SQL.
The fourth parameter length is an optional option that specifies the length of the data type.

The fifth parameter, Driver_options, is an option that provides any database driver-specific options through this parameter.

Examples of parameter bindings using named parameters as placeholders:

<?php

//... Omit the PDO Connection database code

$query = INSERT INTO ContactInfo (Name,address,phone) VALUES (: Name,:address,:p Hone) ";

$stmt = $dbh->prepare ($query);          invokes the prepare () method in the PDO object

 

$stmt->blinparam (': Name ', $name);        Bind the reference of the variable $name to the prepared query name parameter ": Name"

$stmt->blinparam (': Address ', $address);

$stmt->blinparam (':p hone ', phone);

//...

? >

Use a question mark (. As a placeholder for a parameter binding example:

<?php

//... Omit PDO Connection Database code

$query = "INSERT into ContactInfo (Name,address,phone) VALUES (?,?,?)";

$stmt = $dbh->prepare ($query);          Call the Prepare () method in the PDO object

 

$stmt->blinparam (1, $name, PDO::P aram_str);        Bind the reference of the variable $name to the prepared query name parameter ": Name"

$stmt->blinparam (2, $address, PDO::P aram_str);

$stmt->blinparam (3,phone,pdo::P aram_str,20);

//...

? >

3. Execute prepared statement

When the preparation statement is complete and the corresponding parameters are bound, you can repeatedly execute the statements prepared in the database cache by calling the Execute () method in the Pdostatement class object. In the following example, in the ContactInfo table provided earlier, the same INSERT statement is executed sequentially using a preprocessing method, adding two records by changing different parameters. As shown below:

<?php 

try {

     $dbh = new PDO (' Mysql:dbname=testdb;host=localhost ', $username, $passwd);

} catch (Pdoexception $e) {

    echo ' database connection failed: '. $e->getmessage ();

    Exit;

}

 

$query = "INSERT into ContactInfo (Name,address,phone) VALUES (?,?,?)";

$stmt = $dbh->prepare ($query);

 

$stmt->blinparam (1, $name);      

$stmt->blinparam (2, $address);

$stmt->blinparam (3,phone);

 

$name = "Zhao So-and-so";

$address = "Zhongguancun in Haidian District";

$phone = "15801688348";

 

$stmt->execute ();           Prepared statement?> when the execution parameter is bound

If you just want to pass input parameters and have many of these parameters to pass, you will find the shortcut syntax shown below very helpful. is to provide an optional parameter in the Execute () method, which is an array of named parameter placeholders in the prepared query, which is the second way to replace input parameters in execution for a preprocessing query. This syntax allows you to omit calls to $stmt->bindparam (). Make the following modifications to the example above:

<?php 

//... Omit PDO Connection Database code

$query = "INSERT into ContactInfo (Name,address,phone) VALUES (?,?,?)";

$stmt = $dbh->prepare ($query); 

 

Passes an array to the named parameter binding value in the preprocessing query and executes it once.

$stmt->execute ("Zhao So-and-so", "Haidian", "15801688348"));

? >

Also, if you are executing an INSERT statement with an automatically growing ID field in the datasheet, you can use the Lastinsertid () method in the PDO object to get the record ID that was last inserted into the datasheet. If you need to see if other DML statements are performing successfully, you can get the number of rows that affect the record through the rowcount () method in the Pdostatement class object.

Reference Source:
PDO preprocessing statement Pdostatement Object Usage Summary
http://www.lai18.com/content/369336.html Extended reading

"PHP Database PDO series" series of technical Articles sorting collection

1php Database Abstraction Layer PDO

Introduction and simple example of the PDO of 2php database abstraction Layer

3PHP PDO class to resolve database connectivity issues

4PHP 5 Data Object (PDO) abstraction Layer and Oracle

Error analysis of 5PHP pdostatement:bindparam insert data

Analysis of 6PDO anti-injection principle and summary of precautions for using PDO

7PHP Database Abstraction Layer PDO (i)--Introduction and installation Configuration

8PHP Database Abstraction Layer PDO (v)--Error and error handling

9PHP Database Abstraction Layer PDO (iii)--Transaction and Autocommit

10PHP Database Abstraction Layer PDO (ii)--Connection and connection management

The PDO of 11PHP Database Abstraction layer (IV.)--preprocessing statements and stored procedures

12PHP Database Abstraction Layer PDO (vii)--related classes and methods

13PHP Database Abstraction Layer PDO (vi)--large object (lobs)

14PHP PDO Operation Summary

The difference between 15PHP pdostatement objects Bindpram (), Bindvalue () and Bindcolumn

16PDO preprocessing statement Pdostatement Object Usage Summary

Usage and distinction of MySQL, mysqli and PDO in 17PHP "original"

18PHP implementation of PDO MySQL database operation class

19php solution using PDO connection error connection failed SQLState

20php ways to connect and query SQL databases using PDO

21php Connect MSSQL Server database instance using PDO

22php using PDO method

Example of MySQL transaction usage under 23php PDO

24php using PDO to manipulate MySQL database instance

25PHP PDO Fetch mode output list of various parameters

26 A summary of some understandings of PHP PDO

Comparison of PDO and mysqli of database connection modes in 27php

Analysis of 28PDO anti-injection principle and matters needing attention

29php MySQL Connection mode PDO use detailed

30PHP Connect an Access database using PDO

The PDO of 31php in the way of realizing database deletion and modification

32 talking about the ROWCOUNT function of PDO