PDO prepared statement PDOStatement object

This article mainly introduces a summary of the use of PDO preprocessing statements PDOStatement objects. This article introduces the methods of PDOStatement and examples of common methods. Friends in need can refer to

PDO support for preprocessing statements. You need to use the PDOStatement class object, but this class object is not instantiated through the NEW keyword, but is returned directly after preparing a preprocessed SQL statement in the database server through the prepare() method in the PDO object. 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:

PDOStatement::bindColumn — 绑定一列到一个 PHP 变量
PDOStatement::bindParam — 绑定一个参数到指定的变量名
PDOStatement::bindValue — 把一个值绑定到一个参数
PDOStatement::closeCursor — 关闭游标，使语句能再次被执行。
PDOStatement::columnCount — 返回结果集中的列数
PDOStatement::debugDumpParams — 打印一条 SQL 预处理命令
PDOStatement::errorCode — 获取跟上一次语句句柄操作相关的 SQLSTATE
PDOStatement::errorInfo — 获取跟上一次语句句柄操作相关的扩展错误信息
PDOStatement::execute — 执行一条预处理语句
PDOStatement::fetch — 从结果集中获取下一行
PDOStatement::fetchAll — 返回一个包含结果集中所有行的数组
PDOStatement::fetchColumn — 从结果集中的下一行返回单独的一列。
PDOStatement::fetchObject — 获取下一行并作为一个对象返回。
PDOStatement::getAttribute — 检索一个语句属性
PDOStatement::getColumnMeta — 返回结果集中一列的元数据
PDOStatement::nextRowset — 在一个多行集语句句柄中推进到下一个行集
PDOStatement::rowCount — 返回受上一个 SQL 语句影响的行数
PDOStatement::setAttribute — 设置一个语句属性
PDOStatement::setFetchMode — 为语句设置默认的获取模式。

1. Prepare statement

Repeatedly execute a SQL query, using different parameters through each iteration, this In this case, using prepared statements is the 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:

$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 (:), and the parameter The name must be meaningful and preferably the same as the corresponding field name.
Insert statements using question mark (?) parameters as placeholders:

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

The question mark parameters 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 a placeholder is used , you need to replace the input parameters every time it is executed. You can bind parameter variables to prepared placeholders through the bindParam() method in the PDOStatement object (the position or name must correspond). 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. If the placeholder syntax uses name parameters in the prepared query, then the name parameter string is used as bindParam( ) method is provided as the first parameter. 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:

<?php
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(:name,:address,:phone)";
$stmt = $dbh->prepare($query);          //调用PDO对象中的prepare()方法
 
$stmt->blinparam(&#39;:name&#39;,$name);        //将变量$name的引用绑定到准备好的查询名字参数":name"中
$stmt->blinparam(&#39;:address&#39;,$address);
$stmt->blinparam(&#39;:phone&#39;,phone);
//...
?>

Example of parameter binding using question marks (?) as placeholders:

<?php
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query);          //调用PDO对象中的prepare()方法
 
$stmt->blinparam(1,$name,PDO::PARAM_STR);        //将变量$name的引用绑定到准备好的查询名字参数":name"中
$stmt->blinparam(2,$address,PDO::PARAM_STR);
$stmt->blinparam(3,phone,PDO::PARAM_STR,20);
//...
?>

3, Execute prepared statements

When the prepared statements are completed and the corresponding parameters are bound, you can repeatedly execute the prepared statements 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:

<?php 
try {
     $dbh = new PDO(&#39;mysql:dbname=testdb;host=localhost&#39;, $username, $passwd);
}catch (PDOException $e){
    echo &#39;数据库连接失败：&#39;.$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 = "赵某某";
$address = "海淀区中关村";
$phone = "15801688348";
 
$stmt->execute();           //执行参数被绑定后的准备语句
?>

If you are just passing input parameters and have many such parameters to be passed, 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 omit the call to $stmt->bindParam(). Modify the above example as follows:

<?php 
//...省略PDO连接数据库代码
$query = "insert into contactinfo (name,address,phone) values(?,?,?)";
$stmt = $dbh->prepare($query); 
 
//传递一个数组为预处理查询中的命名参数绑定值，并执行一次。
$stmt->execute(array("赵某某","海淀区","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 last insert into the data table. The record ID in . 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.

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Introduction to the import method of thinkPHP2.1 custom tag library

Use pthreads to achieve real PHP multi-threading method

The above is the detailed content of PDO prepared statement PDOStatement object. For more information, please follow other related articles on the PHP Chinese website!