Take you one minute to understand prepared statements and transactions in PHP

This article will introduce you to prepared statements and transactions in PHP. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

PDO operation learning in PHP (2) Preprocessing statements and transactions

In today’s article, let’s do a simple Learn the use of prepared statements and transactions in PDO. They are all operations under the PDO object, and they are not complicated. Simple applications can be easily implemented. But in most cases, everyone is using frames, and there are very few opportunities for handwriting.

Preprocessed statement function

A prepared statement is to prepare a statement to be executed and then return a PDOStatement object. Generally we will use the execute() method of the PDOStatement object to execute this statement. Why is it called preprocessing? Because it allows us to call this statement multiple times and replace the field conditions in the statement with placeholders. Compared to query() or exec() using PDO objects directly, preprocessing is more efficient and allows the client/server to cache queries and meta-information. Of course, the more important point is that the application of placeholders can effectively prevent basic SQL injection attacks. We do not need to manually add quotes to SQL statements and directly let preprocessing solve this problem. I believe everyone can learn this. Past knowledge is also one of the most common questions we see during interviews.

// 使用 :name 形式创建一个只进游标的 PDOStatement 对象
$stmt = $pdo->prepare("select * from zyblog_test_user where username = :username", [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);

var_dump($stmt);
// object(PDOStatement)#2 (1) {
//     ["queryString"]=>
//     string(57) "select * from zyblog_test_user where username = :username"
//   }

$stmt->execute([':username' => 'aaa']);
$aUser = $stmt->fetchAll();

$stmt->execute([':username' => 'bbb']);
$bUser = $stmt->fetchAll();

var_dump($aUser);
// array(1) {
//     [0]=>
//     array(8) {
//       ["id"]=>
//       string(1) "1"
//       [0]=>
//       string(1) "1"
//       ["username"]=>
//       string(3) "aaa"
//       ……

var_dump($bUser);
// array(1) {
//     [0]=>
//     array(8) {
//       ["id"]=>
//       string(1) "2"
//       [0]=>
//       string(1) "2"
//       ["username"]=>
//       string(3) "bbb"
//       ……

The first parameter of the prepare() method is the SQL statement we need to execute. In this code, we use a placeholder in the form of:xxx, so when calling the prepare() method, it returns We need to specify the placeholder value when executing the execute() method of the PDOStatement object. In the code, we use this SQL statement to implement two queries by replacing different placeholder contents.

The second parameter of the prepare() method is the property set for the returned PDOStatement object. Common usage is: setting PDO::ATTR_CURSOR to PDO::CURSOR_SCROLL will get a scrollable cursor.

Some drivers have driver-level options, which are set during prepare. PDO::ATTR_CURSOR is the type that sets the database cursor, and PDO::CURSOR_FWDONLY means to create a PDOStatement object that only enters the cursor. This is the default cursor option because this cursor is the fastest and the most common data access mode in PHP. For knowledge about database cursors, you can check the relevant content by yourself.

In addition, PDOStatement can also bind placeholder data through the bindParam() method. We will continue to learn in the following articles related to PDOStatement objects.

Next, let’s take a look at using the ? placeholder to implement the query. The ? placeholder is bound in the form of a subscript when binding.

// 使用 ? 形式创建一个只进游标的 PDOStatement 对象
$stmt = $pdo->prepare("select * from zyblog_test_user where username = ?", [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);

$stmt->execute(['aaa']);
$aUser = $stmt->fetchAll();

var_dump($aUser);
// array(1) {
//     [0]=>
//     array(8) {
//       ["id"]=>
//       string(1) "1"
//       [0]=>
//       string(1) "1"
//       ["username"]=>
//       string(3) "aaa"
//       ……

Of course, this kind of precompiled statement is not limited to query statements. It can be added, deleted, or modified, and it also supports placeholders. Prepared statements for manipulating databases in PHP This article has detailed examples.

Transaction Capability

Everyone must have a certain understanding of transactions, so I won’t introduce specific concepts here. Let’s just look at transactions in PDO. How is it achieved. First, let's look at what happens when there are no transactions.

$pdo->exec("insert into tran_innodb (name, age) values ('Joe', 12)"); // 成功插入

$pdo->exec("insert into tran_innodb2 (name, age) values ('Joe', 12)"); // 报错停止整个PHP脚本执行
// Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'blog_test.tran_innodb2' doesn't exist

Assume that these two tables need to be updated at the same time, but the second statement reports an error. In the absence of a transaction, our first piece of data will be successfully inserted normally, which is not the result we need. At this time, we need the help of transaction capabilities, so that we can make the two tables either succeed or fail at the same time.

try {
    // 开始事务
    $pdo->beginTransaction();

    $pdo->exec("insert into tran_innodb (name, age) values ('Joe', 12)");
    $pdo->exec("insert into tran_innodb2 (name, age) values ('Joe', 12)"); // 不存在的表

    // 提交事务
    $pdo->commit();
} catch (Exception $e) {
    // 回滚事务
    $pdo->rollBack();
    // 输出报错信息
    echo "Failed: " . $e->getMessage(), PHP_EOL;
    // Failed: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'blog_test.tran_innodb2' doesn't exist
}

The first is the beginTransaction() method, which is used to turn off automatic submission of the database and start a transaction. After this method, it will only be closed after encountering the commit() or rollBack() method. affairs.

The commit() method is to package and submit all data operations after beginTransaction() if there are no accidents during the operation.

rollBack() is to roll back data. When there is a problem with a statement or code after beginTransaction(), the previous data operation is rolled back to ensure that all statements after beginTransaction() are either successful or not. fail.

These three simple functions complete the entire transaction operation for us. We will discuss the in-depth study of transactions in the future when we study MySQL in depth. What we need to note here is that it is best for the PDO object to specify the error mode as throwing an exception. If the error mode is not specified, errors in the transaction will not be reported directly, but will return an error code. We need to use the error code to Determine whether to commit or rollback. This is far less concise and intuitive than the exception mechanism.

总结

我们简单的梳理并学习了一下 PDO 中的预处理和事务相关的知识，接下来就要进入 PDOStatement 对象相关内容的学习。PDOStatement 对象就是 PDO 的预处理对象，也就是在日常开发中我们会接触到的最多的数据操作对象。这块可是重点内容，大家可不能松懈了哦！

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202008/source/PHP%E4%B8%AD%E7%9A%84PDO%E6%93%8D%E4%BD%9C%E5%AD%A6%E4%B9%A0%EF%BC%88%E4%BA%8C%EF%BC%89%E9%A2%84%E5%A4%84%E7%90%86%E8%AF%AD%E5%8F%A5%E5%8F%8A%E4%BA%8B%E5%8A%A1.php

推荐学习：php视频教程

The above is the detailed content of Take you one minute to understand prepared statements and transactions in PHP. For more information, please follow other related articles on the PHP Chinese website!