PDO preprocessing statement PDOStatement object usage summary_php

This article mainly introduces the summary of the use of PDOStatement objects in PDO preprocessing statements. This article introduces the PDOStatement method and examples of common methods, if you need a PDOStatement class object, you need to use the PDOStatement class object to support the PDO preprocessing statements. However, this class object is not instantiated by the NEW keyword, but through the prepare () method in the PDO object, prepare a pre-processed SQL statement on the database server and then return it directly. If the PDOStatement class object returned by the query () method in the PDO object is previously executed, only one result set object is returned. If the PDOStatement class object generated by executing the prepare () method in the PDO object is a query object, parameterized SQL commands can be defined and executed. All member methods in the PDOStatement class are as follows:

The code is as follows:

PDOStatement: bindColumn-bind a column to a PHP variable
PDOStatement: bindParam-bind a parameter to the specified variable name
PDOStatement: bindValue-bind a value to a parameter
PDOStatement: closeCursor-close the cursor so that the statement can be executed again.
PDOStatement: columnCount-number of columns in the returned result set
PDOStatement: debugDumpParams-print an SQL preprocessing command
PDOStatement: errorCode-obtain the SQLSTATE related to the last statement handle operation
PDOStatement: errorInfo-get the extension error information related to the last statement handle operation
PDOStatement: execute-execute a preprocessing statement
PDOStatement: fetch-get the next row from the result set
PDOStatement: fetchAll-returns an array containing all rows in the result set.
PDOStatement: fetchColumn-returns a separate column from the next row in the result set.
PDOStatement: fetchObject-get the next row and return it as an object.
PDOStatement: getAttribute-retrieve a statement attribute
PDOStatement: getColumnMeta-returns the metadata of a column in the result set.
PDOStatement: nextRowset-push to the next row set in a multi-row set statement handle
PDOStatement: rowCount-returns the number of rows affected by the previous SQL statement.
PDOStatement: setAttribute-set a statement attribute
PDOStatement: setFetchMode-set the default acquisition mode for the statement.

1. prepare statements

Repeat an SQL query and use different parameters for each iteration. in this case, the precondition statement is the most efficient. To use a pre-processing statement, you must first prepare an "SQL statement" on the database server, but you do not need to execute it immediately. PDO supports using the placeholder syntax to bind variables to the pre-processing SQL statement. For a prepared SQL statement, if you want to change some column values during each execution, you must use a placeholder instead of a specific column value. There are two types of syntax for using placeholders in PDO: "Name parameter" and "question mark parameter". which syntax is used depends on your preferences.

INSERT statements using named parameters as placeholders:

The code is as follows:

$ Dbh-> prepare ("insert into contactinfo (name, address, phone) values (: name,: address,: phone )");

You need to customize a string as a "name parameter". each name parameter must start with a colon (:). the name of the parameter must be meaningful. it is best to be the same as the name of the corresponding field.
Use question mark (?) INSERT statement with parameters as placeholders:

The code is as follows:

$ Dbh-> prepare ("insert into contactinfo (name, address, phone) values (?,?,?)");

The question mark parameter must correspond to the field location order. No matter which parameter is used as a placeholder for a query or a placeholder is not used in a statement, you must use the prepare () method in the PDO object to prepare the query to be used for iterative execution, and return the PDOStatement class object.

2. bind parameters

After the SQL statement is prepared on the database server using the prepare () method in the PDO object, if placeholders are used, the input parameters need to be replaced during each execution. You can use the bindParam () method in the PDOStatement object to bind the parameter variable to the prepared placeholder (the position or name must correspond ). The bindParame () method is prototype as follows:

The code is as follows:

Bool PDOStatement: bindParam (mixed $ parameter, mixed & $ variable [, int $ data_type = PDO: PARAM_STR [, int $ length [, mixed $ driver_options])

The first parameter is required. if the placeholder syntax in the prepared query uses the name parameter, the name parameter string is provided as the first parameter of the bindParam () method. If the placeholder syntax uses the question mark parameter, the index offset of the column value placeholder in the prepared query is used as the first parameter of this method.

The second parameter variable is also optional, providing the value of the placeholder specified by the first parameter. Because this parameter is passed by reference, only variables can be provided as parameters, and numerical values cannot be provided directly.

The third parameter data_type is optional and sets the data type for the currently bound parameter. It can be the following values.

PDO: PARAM_BOOL indicates the boolean data type.
PDO: PARAM_NULL indicates the NULL type in SQL.
PDO: PARAM_INT indicates the INTEGER data type in SQL.
PDO: PARAM_STR represents the CHAR, VARCHAR, and other string data types in SQL.
PDO: PARAM_LOB represents the data type of large objects in SQL.

The fourth parameter length is optional and is used to specify the length of the data type.

The fifth parameter driver_options is optional. it provides any database driver-specific options.
Example of using named parameters as placeholders for parameter binding:

The code is as follows:

<? Php
//... The PDO connection code is omitted.
$ Query = "insert into contactinfo (name, address, phone) values (: name,: address,: phone )";
$ Stmt = $ dbh-> prepare ($ query); // call 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 (': phone', phone );
//...
?>

Use question mark (?) Example of parameter binding as a placeholder:

The code is as follows:

<? Php
//... The PDO connection code is omitted.
$ 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: PARAM_STR); // bind a reference to the variable $ name to the prepared query name parameter ": name ".
$ Stmt-> blinparam (2, $ address, PDO: PARAM_STR );
$ Stmt-> blinparam (3, phone, PDO: PARAM_STR, 20 );
//...
?>

3. execute the prepared statement

After the prepared statement is complete and corresponding parameters are bound, you can call the execute () method in the PDOStatement class object to execute the prepared statements in the database cache repeatedly. In the following example, in the contactinfo table provided in the forward, the same INSERT statement is continuously executed using preprocessing and two records are added by changing different parameters. As follows:

The code is as follows:

<? 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 Moumou ";
$ Address = "Zhongguancun, Haidian district ";
$ Phone = "15801688348 ";

$ Stmt-> execute (); // prepare the statement after the execution parameter is bound
?>

If you only want to pass the input parameters, and many such parameters need to be passed, you will find the shortcut syntax shown below very helpful. By providing an optional parameter in the execute () method, this parameter is an array consisting of named parameter placeholders in the preparation query, this is the second method to replace input parameters in the pre-processing query execution. This syntax allows you to save calls to $ stmt-> bindParam. Modify the preceding example as follows:

The code is as follows:

<? Php
//... The PDO connection code is omitted.
$ Query = "insert into contactinfo (name, address, phone) values (?,?,?) ";
$ Stmt = $ dbh-> prepare ($ query );

// Pass an array to bind the value to the name parameter in the pre-processing query and execute it once.
$ Stmt-> execute (array ("Zhao Moumou", "Haidian District", "15801688348 "));
?>

In addition, if an INSERT statement is executed and the data table contains an automatically increasing ID field, you can use the lastinsertId () method in the PDO object to obtain the record ID in the last inserted data table. To check whether other DML statements are successfully executed, you can use the rowCount () method in the PDOStatement class object to obtain the number of rows that affect the record.