PDO prepared statement PDOStatement object usage summary, pdopdostatement

Summary of the use of PDO prepared statements PDOStatement objects, pdopdostatement

PDO’s support for prepared statements requires the use of PDOStatement class objects, but this class object is not instantiated through the NEW keyword, but is prepared in the database server through the prepare() method in the PDO object. Returned directly after the preprocessed SQL statement. If the PDOStatement class object returned by previously executing the query() method in the PDO object only represents a result set object. And if the PDOStatement class object generated by executing the prepare() method in the PDO object is a query object, it can define and execute parameterized SQL commands. All member methods in the PDOStatement class are as follows:

Copy code 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 — Closes the cursor so that the statement can be executed again.
PDOStatement::columnCount — Returns the number of columns in the result set
PDOStatement::debugDumpParams — Print a SQL preprocessing command
PDOStatement::errorCode — Get the SQLSTATE
related to the last statement handle operation PDOStatement::errorInfo — Get extended error information related to the last statement handle operation
PDOStatement::execute — execute a prepared 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 single column from the next row in the result set.
PDOStatement::fetchObject — Gets the next row and returns it as an object.
PDOStatement::getAttribute — Retrieve a statement attribute
PDOStatement::getColumnMeta — Returns metadata for a column in the result set
PDOStatement::nextRowset — Advance to the next rowset in a multi-rowset 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 fetch mode for statements.

1. Prepare statements

Repeatedly execute a SQL query, using different parameters for each iteration. In this case, prepared statements are most efficient. To use prepared statements, you first need to prepare "a SQL statement" in the database server, but it does not need to be executed immediately. PDO supports the use of "placeholder" syntax to bind variables to this preprocessed SQL statement. For a prepared SQL statement, if some column values need to be changed each time it is executed, "placeholders" must be used instead of specific column values. There are two syntaxes for using placeholders in PDO: "named parameters" and "question mark parameters". Which syntax to use depends on personal preference.

INSERT statement using named parameters as placeholders:

Copy code 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 "named parameter". Each named parameter needs to start with a colon (:). The naming of the parameter must be meaningful, and it is best to have the same name as the corresponding field name.
INSERT statement using question mark (?) parameter as placeholder:

Copy code The code is as follows:

$dbh->prepare(“insert into contactinfo(name,address,phone) values(?,?,?)”);

The question mark parameter must correspond to the position order of the fields. No matter which parameter is used as a query composed of placeholders, or no placeholders are used in the statement, you need to use the prepare() method in the PDO object to prepare the query that will be used for iterative execution, and Returns a PDOStatement class object.

2. Binding parameters

When the SQL statement is prepared on the database server through the prepare() method in the PDO object, if placeholders are used, the input parameters need to be replaced each time it is executed. You can bind parameter variables to prepared placeholders (the position or name must correspond) through the bindParam() method in the PDOStatement object. The prototype of the method bindParame() is as follows:

Copy code 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 parameter is required. If the placeholder syntax uses named parameters in the prepared query, then the named parameter string is provided as the first parameter of the bindParam() method. If the placeholder syntax uses a question mark argument, then the index offset of the column value placeholder in the prepared query is passed as the first argument to the method.

The second parameter variable is also optional and provides the value of the placeholder specified by the first parameter. Because the parameter is passed by reference, only variables can be provided 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 represents 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 is used to specify the length of the data type.

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

Copy code The code is as follows:

//...Omit the PDO connection database code
$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 variable $name to the prepared query name parameter ":name"
$stmt->blinparam(':address',$address);
$stmt->blinparam(':phone',phone);
//...
?>

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

Copy code The code is as follows:

//...Omit the 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::PARAM_STR); //Bind the reference of 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 prepared statements

When the prepared statement is completed and the corresponding parameters are bound, you can repeatedly execute the statement prepared in the database cache by calling the execute() method in the PDOStatement class object. In the following example, preprocessing is used to continuously execute the same INSERT statement in the contactinfo table provided earlier, and two records are added by changing different parameters. As shown below:

Copy code The code is as follows:

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 XX";
$address = "Zhongguancun, Haidian District";
$phone = "15801688348";

$stmt->execute(); //The prepared statement after the execution parameters are bound
?>

If you are just passing input parameters and have many such parameters to pass, then you will find the shortcut syntax shown below very helpful. This is the second way to replace input parameters for a preprocessed query during execution by providing an optional parameter in the execute() method, which is an array of named parameter placeholders in the prepared query. This syntax allows you to eliminate the call to $stmt->bindParam(). Modify the above example as follows:

Copy code The code is as follows:

//...Omit the PDO connection database code
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query);

//Pass an array to bind values to the named parameters in the preprocessed query and execute it once.
$stmt->execute(array("Zhao XX","Haidian District","15801688348"));
?>

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