PDO preprocessing statement PDOStatement object usage summary
- 2021-08-03 09:51:50
- OfStack
The support of PDO for preprocessing statements needs PDOStatement class objects, but this kind of objects are not instantiated by NEW keywords, but returned directly after a preprocessing SQL statement is prepared in the database server by prepare () method in PDO objects. If the PDOStatement class object is returned by executing the query () method in the PDO object earlier, it represents only one result set object. If the PDOStatement class object is generated by executing the prepare () method in the PDO object, it is a query object, which can define and execute parameterized SQL commands. All member methods in the PDOStatement class are as follows:
PDOStatement::bindColumn - Binding 1 Column to 1 A PHP Variable
PDOStatement::bindParam - Binding 1 Parameters to the specified variable name
PDOStatement::bindValue - Put 1 Values bound to the 1 Parameters
PDOStatement::closeCursor - Close the cursor so that the statement can be executed again.
PDOStatement::columnCount - Returns the number of columns in the result set
PDOStatement::debugDumpParams - Print 1 Article SQL Preprocessing command
PDOStatement::errorCode - Get to keep up 1 Substatement handle operation related SQLSTATE
PDOStatement::errorInfo - Get to keep up 1 Extended error messages related to substatement handle operations
PDOStatement::execute - Execute 1 Bar preprocessing statement
PDOStatement::fetch - Get the following from the result set 1 Row
PDOStatement::fetchAll - Return 1 An array containing all the rows in the result set
PDOStatement::fetchColumn - From the following in the result set 1 Row returns a separate 1 Column.
PDOStatement::fetchObject - Get the following 1 Row and act as 1 Objects are returned.
PDOStatement::getAttribute - Retrieval 1 Statement attributes
PDOStatement::getColumnMeta - Return to the result set 1 Metadata for columns
PDOStatement::nextRowset - In 1 In the handles of multirowset statements, advance to the following 1 Rowset
PDOStatement::rowCount - Return to receive 1 A SQL Number of rows affected by statement
PDOStatement::setAttribute - Settings 1 Statement attributes
PDOStatement::setFetchMode - Sets the default get mode for the statement.
1. Prepare the statement
Execute one SQL query repeatedly, using different parameters for each iteration, which is the most efficient case using preprocessing statements. To use preprocessing statements, you first need to prepare "1 SQL statement" in the database server, but you don't need to execute it immediately. PDO supports the use of a "placeholder" syntax to bind variables into this preprocessed SQL statement. For a prepared SQL statement, if a number of column values are to be changed each time it is executed, a "placeholder" must be used instead of the specific column value. There are two syntax for using placeholders in PDO: "named parameter" and "question mark parameter". Which syntax to use depends on personal preference.
INSERT insertion statement using named parameters as placeholders:
$dbh->prepare( " insert into contactinfo(name,address,phone) values(:name,:address,:phone) " );
You need to customize 1 string as a "named parameter". Each named parameter needs to start with a colon (:). The name 1 of the parameter must be meaningful, preferably the same as the corresponding field name.
Use the question mark (? ) Parameter as a placeholder for the INSERT insertion statement:
$dbh->prepare( " insert into contactinfo(name,address,phone) values(?,?,?) " );
Question mark parameter 1 must correspond to the position order of the fields. No matter which parameter is used as a placeholder for the query, or no placeholder is used in the statement, the prepare () method in the PDO object needs to be used to prepare the query to be used for iterative execution and return the PDOStatement class object.
2. Binding parameters
When the SQL statement is ready on the database server side through the prepare () method in the PDO object, if placeholders are used, the input parameters need to be replaced each time they are executed. Parameter variables can be bound to prepared placeholders (where or where the name corresponds) through the bindParam () method in the PDOStatement object. The prototype of the method bindParame () is as follows:
bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )
The first parameter parameter is required, and if the placeholder syntax uses the name parameter in the prepared query, the name parameter string is supplied 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 taken as the first parameter of the method.
The second parameter, variable, is also optional and provides the value for the placeholder specified by the first parameter. Because this parameter is passed by reference, you can only supply variables as parameters, not values directly.
The third parameter, data_type, is optional and sets the data type for the currently bound parameter. Can be the following values.
PDO:: PARAM_BOOL represents the boolean data type.
PDO:: PARAM_NULL stands for the NULL type in SQL.
PDO:: PARAM_INT represents the INTEGER data type in SQL.
PDO:: PARAM_STR represents CHAR, VARCHAR, and other string data types in SQL.
PDO:: PARAM_LOB represents the large object data type in SQL.
The fourth parameter, length, is optional and specifies the length of the data type.
The fifth parameter, driver_options, is optional and provides any database driver-specific options.
Example of parameter binding using named parameters as placeholders:
<?php
//... Omission PDO Connection database code
$query = "insert into contactinfo (name,address,phone) values(:name,:address,:phone)";
$stmt = $dbh->prepare($query); // Call PDO Object in the prepare() Method
$stmt->blinparam(':name',$name); // Will the variable $name To the prepared query name parameter ":name" Medium
$stmt->blinparam(':address',$address);
$stmt->blinparam(':phone',phone);
//...
?>
Use the question mark (?) Example of parameter binding as placeholder:
<?php
//... Omission PDO Connection database code
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query); // Call PDO Object in the prepare() Method
$stmt->blinparam(1,$name,PDO::PARAM_STR); // Will the variable $name To the prepared query name parameter ":name" Medium
$stmt->blinparam(2,$address,PDO::PARAM_STR);
$stmt->blinparam(3,phone,PDO::PARAM_STR,20);
//...
?>
3. Execute the preparation statement
When the prepared statement is complete and the corresponding parameters are bound, you can repeatedly execute the prepared statement in the database cache by calling the execute () method in the PDOStatement class object. In the following example, the same INSERT statement is executed sequentially using preprocessing to the contactinfo table provided earlier, 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 Moumou ";
$address = " Zhongguancun, Haidian District ";
$phone = "15801688348";
$stmt->execute(); // Execute the preparation statement after the parameters are bound
?>
If you are passing only input parameters and there are many such parameters to pass, you will find the shortcut syntax shown below very helpful. By supplying an optional parameter in the execute () method, which is an array of named parameter placeholders in the prepared query, this is the second way to replace input parameters in execution for preprocessed queries. This syntax allows you to save the $stmt- > The call to bindParam (). Modify the above example as follows:
<?php
//... Omission PDO Connection database code
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query);
// Transfer 1 Arrays are bound values for named parameters in the preprocessing query, and execute 1 Times.
$stmt->execute(array(" Zhao Moumou "," Haidian District ","15801688348"));
?>
In addition, if you are executing an INSERT statement and have an ID field that grows automatically in the data table, you can use the lastinsertId () method in the PDO object to get the record ID that was last inserted into the data table. If you need to see if other DML statements are executed successfully, you can get the number of rows that affect the record through the rowCount () method in the PDOStatement class object.