How to use mysqli transactions and prepared statements in PHP

For MySQLi, transactions and prepared statements are of course the capital that enables it to eliminate MySQL extensions. We have also learned about transactions and prepared statements in PDO before. So here, we will not talk about the theory anymore, and go directly to the code to see the difference in the use of these two features in MySQLi and PDO.

Transaction processing

First of all, we still want MySQLi to report exceptions for incorrect statements. This function is very different from PDO. In PDO, we can directly specify the error reporting attribute of the connection. In MySQLi, we need to specify the error attribute in the MySQLi_Driver object to throw an exception. Obviously, MySQLi_Driver is the driver object of MySQLi.

// 使用异常处理错误情况
$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

This specifies that when using the MySQLi extension, all error messages will be thrown as exceptions.

The following content is actually very similar to PDO.

try {
    // 开始事务
    $mysqli->begin_transaction();

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

    // 提交事务
    $mysqli->commit();

} catch (Exception $e) {
    // 回滚事务
    $mysqli->rollback();

    var_dump($e->getMessage());
    // string(44) "Table 'blog_test.tran_innodb2' doesn't exist"
}

We also use a begin_transaction() to start the transaction. Then commit the transaction through the commint() method. In this test code, the second SQL statement will report an error, so it enters the catch and uses rollback() to roll back the transaction.

Preprocessing statements

Generally speaking, there is not much difference between transaction processing and PDO, but there are some differences between the use of prepared statements and PDO. . The first is that the placeholders in our MySQLi are only ? question mark placeholders. In addition, only bind_param() is not similar to the bindValue() method in PDO.

$stmt = $mysqli->prepare("select * from zyblog_test_user where username = ?");

$username = 'aaa';
$stmt->bind_param("s", $username); // 绑定参数
$stmt->execute(); // 执行语句
$aUser = $stmt->fetch(); // 获取mysqli_result结果集对象

$username='bbb';
$stmt->bind_param('s', $username);
$stmt->execute();
$bUser = $stmt->fetch();

var_dump($aUser);
// array(4) {
//     ["id"]=>
//     int(1)
//     ["username"]=>
//     string(3) "aaa"
//     ["password"]=>
//     string(3) "aaa"
//     ["salt"]=>
//     string(3) "aaa"
//   }

var_dump($bUser);
// array(4) {
//     ["id"]=>
//     int(2)
//     ["username"]=>
//     string(3) "bbb"
//     ["password"]=>
//     string(3) "bbb"
//     ["salt"]=>
//     string(3) "123"
//   }

As can be seen from the code, the use of bind_param() method is also very different from PDO. It does not require a subscript, but is given an s parameter. This parameter indicates the type of bound data, s is the string type. We will learn more about other types when we study MySQLi_STMT related content.

Summary

In fact, from the code level, most of the content is very similar to PDO, but some parameters are different. For us, it is still more about learning and understanding, so as not to get confused when encapsulating or using some frameworks that use MySQLi as the underlying database operation.

Test code:

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/7.PHP中的MySQLi扩展学习（四）mysqli的事务与预处理语句.php

Recommended learning: php video tutorial

The above is the detailed content of How to use mysqli transactions and prepared statements in PHP. For more information, please follow other related articles on the PHP Chinese website!