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:
Copy Code code 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-closes the cursor so that the statement can be executed again.
Pdostatement::columncount-returns the number of columns in the result set
Pdostatement: Print a SQL preprocessing command:d ebugdumpparams-
Pdostatement::errorcode-gets 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 a column in the result set
Pdostatement::nextrowset-pushes to the next rowset in a multiline Set statement handle
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:
Copy Code code as follows:
$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:
Copy Code code as follows:
$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:
Copy Code code as follows:
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:
Copy Code code as follows:
<?php
//... Omitting PDO Connection database code
$query = "INSERT into ContactInfo (Name,address,phone) VALUES (: Name,:address,:p hone)";
$stmt = $dbh->prepare ($query); Call the Prepare () method in the PDO object
$stmt->blinparam (': Name ', $name); To bind a reference to a 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:
Copy Code code as follows:
<?php
//... Omitting 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); To bind a reference to a 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:
Copy Code code 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 So-and-so";
$address = "Zhongguancun in Haidian District";
$phone = "15801688348";
$stmt->execute (); Prepared statement after the execution of a 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:
Copy Code code as follows:
<?php
//... Omitting 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.
