Interact with MySQL database through PDO extension to implement addition, deletion, modification and query implementation and database transactions

Related learning recommendations: mysql tutorial

Add, delete, modify and query through preprocessing statements

Why use prepared statements

We have briefly introduced prepared statements in the previous tutorial. We can compare them with view templates. The so-called prepared statements are predefined SQL statement templates. The specific Parameter values ​​are replaced by placeholders:

INSERT INTO REGISTRY (name, value) VALUES (?, ?)
INSERT INTO REGISTRY (name, value) VALUES (:name, :value)

Then before the subsequent SQL statement is actually executed, the specific parameter values ​​are bound and mapped to the corresponding placeholders through specific API methods. Just like the defined view template replaces the variables with specific placeholders, and then passes the variable values ​​in to fill and render during actual rendering.

Why go to all this trouble? Wouldn’t it be nice to directly use the query method demonstrated earlier to perform addition, deletion, modification, and query operations? Well, let’s talk about the benefits of prepared statements, or why we should use prepared statements for database interaction. There are two benefits:

• First, use prepared statements to define SQL in advance The template will only be parsed once, but can be executed multiple times by passing different parameter values, thereby avoiding repeated analysis, compilation and optimization of the same SQL statement in the template, and improving the execution speed of database operations; The parameter values ​​of the processing statement will be processed by the underlying driver, thus effectively avoiding SQL injection attacks.
• To sum up, from the perspective of performance and security, it is recommended to use prepared statements to handle the addition, deletion, modification and query operations of the database.

Add, delete, modify, and query sample code

Next, we implement the add, delete, modify, and query operations of the MySQL database based on the prepared statement API provided by PDO. We will implement it in an object-oriented manner:

<?php class Post
{
    public $id;
    public $title;
    public $content;
    public $created_at;

    /**
     * @var PDO
     */
    protected $pdo;

    public function __construct(PDO $pdo = null)
    {
        if ($pdo != null) {
            $this->pdo = $pdo;
        }
    }

    public function insert($title, $content)
    {
        $sql = 'INSERT INTO `post` (title, content, created_at) VALUES (:title, :content, :created_at)';
        try {
            // 准备预处理语句
            $stmt = $this->pdo->prepare($sql);
            // 获取当前时间对应的格式化字符串：2020-05-28 13:00:00
            $datetime = date('Y-m-d H:i:s', time());
            // 绑定参数值
            $stmt->bindParam(':title', $title, PDO::PARAM_STR);
            $stmt->bindParam(':content', $content, PDO::PARAM_STR);
            $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
            // 执行语句
            $stmt->execute();
            return $this->pdo->lastInsertId();  // 返回插入记录对应ID
        } catch (PDOException $e) {
            printf("数据库插入失败: %s\n", $e->getMessage());
        }
    }

    public function select($id)
    {
        $sql = 'SELECT * FROM `post` WHERE id = ?';
        try {
            // 准备预处理语句
            $stmt = $this->pdo->prepare($sql);
            // 绑定参数值
            $stmt->bindValue(1, $id, PDO::PARAM_INT);
            // 执行语句
            $stmt->execute();
            return $stmt->fetchObject(self::class);  // 以对象方式返回结果集
        } catch (PDOException $e) {
            printf("数据库查询失败: %s\n", $e->getMessage());
        }
    }

    public function selectAll()
    {
        $sql = 'SELECT * FROM `post` ORDER BY id DESC';
        try {
            // 准备预处理语句
            $stmt = $this->pdo->prepare($sql);
            // 执行语句
            $stmt->execute();
            return $stmt->fetchAll();  // 返回所有结果集
        } catch (PDOException $e) {
            printf("数据库查询失败: %s\n", $e->getMessage());
        }
    }

    public function update($id)
    {
        $sql = 'UPDATE `post` SET created_at = :created_at WHERE id = :id';
        try {
            // 准备预处理语句
            $stmt = $this->pdo->prepare($sql);
            $datetime = date('Y-m-d H:i:s', time());
            // 绑定参数值
            $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);
            // 执行语句
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            printf("数据库更新失败: %s\n", $e->getMessage());
        }
    }

    public function delete($id)
    {
        $sql = 'DELETE FROM `post` WHERE id = ?';
        try {
            // 准备预处理语句
            $stmt = $this->pdo->prepare($sql);
            // 绑定参数值
            $stmt->bindValue(1, $id, PDO::PARAM_INT);
            // 执行语句
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            printf("数据库删除失败: %s\n", $e->getMessage());
        }
    }
}

We built a

Post

class, and then initialized the $pdo instance in the constructor (passed in from the outside), and then added, deleted, and modified based on prepared statements. The check operation is decomposed into the corresponding class method. The overall logic is very simple. Take insert as an example. First, the SQL template is passed in through the prepare method of the PDO object to construct the prepared statement. This method returns the PDOStatement object. Next, the bindParam method of the object is called. Determine the specific parameter value. The first parameter of this method is the placeholder, the second parameter is the parameter value, and the third parameter is the value type (the corresponding constant can be queried in PDO predefined constants). Bind the parameters. After that, you can call the execute method of the PDOStatement object to execute the prepared statement. For insert operations, you can use the lastInsertId method on the PDO object to return the primary key ID of the inserted record. For update and delete methods, you can use the rowCount method on the PDOStatement object to return the number of affected rows to indicate whether the operation was successful. For query operations, a single record can be returned through the fetch method of the PDOStatement object, or an object instance mapped to a specified class (also a single record) can be returned through the fetchObject method. For multiple results, it can be returned through the fetchAll method.

It should be noted that when declaring prepared statements, you can use the

?

placeholder or :name, which is more readable. placeholder, and then when binding parameters, you can either pass the bindValue or the bindParam method. The parameters passed in both are the same, but for the ? placeholder, you need to establish the relationship with the SQL template through the numerical sequence number. Mapping (starting from 1). It is not difficult to understand the above code by combining the code and PHP official documentation. Next, let’s write the test code:

// 初始化 PDO 连接实例
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8mb4';
$user = 'root';
$pass = 'root';
try {
    $pdo = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    printf("数据库连接失败: %s\n", $e->getMessage());
}

// 测试代码
$post = new Post($pdo);
// insert
$title = '这是一篇测试文章';
$content = '测试内容: 今天天气不错';
$id = $post->insert($title, $content);
echo '文章插入成功: ' . $id . '<br>';
// select
$item = $post->select($id);
echo '<pre class="brush:php;toolbar:false">';
print_r($item);
// update
$affected = $post->update($id);
echo '受影响的行数: ' . $affected . '<br>';
// delete
$affected = $post->delete($id);
echo '受影响的行数: ' . $affected . '<br>';
// selectAll
$items = $post->selectAll();
print_r($items);

Initialize a PDO object instance and pass it in

Post

constructor, and then calls the add, delete, modify, and query methods of the Post object in sequence. Accessed in a browser, the printed result is as follows:

We can see that the result set array returned by the

fetchAll

method by default contains both index mapping and Contains field name mapping, which can be solved by setting the acquisition mode. For example, if you want to return the Post object array, you can do this: <pre class="brush:php;toolbar:false">return $stmt-&gt;fetchAll(PDO::FETCH_CLASS, self::class);</pre>In this way, the returned result is like this:

For more mode settings, please refer to the introduction and examples of the fetchAll method in the official documentation.

Database Transaction

Finally, let’s take a look at how to implement database transaction submission and rollback through PDO extension. We already know that for a single SQL statement, transaction submission and rollback are It is completed automatically. For a sequence of SQL statements (multiple SQL statements), you need to explicitly start the transaction and submit the transaction. The PDO object also provides corresponding API methods for this. It’s very simple. For example, we add a batch insertion method in the

Post

class. batchInsert method: <pre class="brush:php;toolbar:false">public function batchInsert(array $items) {     $sql = 'INSERT INTO `post` (title, content, created_at) VALUES (:title, :content, :created_at)';     try {         // 开启事务         $this-&gt;pdo-&gt;beginTransaction();         // 准备预处理语句         $stmt = $this-&gt;pdo-&gt;prepare($sql);         foreach ($items as $item) {             // 绑定参数值             $datetime = date('Y-m-d H:i:s', time());             $stmt-&gt;bindParam(':title', $item-&gt;title, PDO::PARAM_STR);             $stmt-&gt;bindParam(':content', $item-&gt;content, PDO::PARAM_STR);             $stmt-&gt;bindParam(':created_at', $datetime, PDO::PARAM_STR);             // 执行语句             $stmt-&gt;execute();         }         $this-&gt;pdo-&gt;commit(); // 提交事务         return $stmt-&gt;rowCount();  // 返回受影响的行数     } catch (PDOException $e) {         $this-&gt;pdo-&gt;rollBack(); // 回滚事务         printf(&quot;数据库批量插入失败: %s\n&quot;, $e-&gt;getMessage());     } }</pre> <p>我们只需要在执行 SQL 序列之前调用 PDO 对象的 beginTransaction 方法开启事务，然后在所有 SQL 语句执行完成后调用 commit 方法提交事务，如果 SQL 执行过程中出错，则在异常处理代码中通过 PDO 对象的 rollBack 方法回滚事务。</p> <p>为上述方法编写测试代码：</p> <pre class="brush:php;toolbar:false">$post = new Post($pdo); $items = [     [         'title' =&gt; '这是一篇测试文章111',         'content' =&gt; '测试内容'     ],     [         'title' =&gt; '这是一篇测试文章222',         'content' =&gt; '测试内容'     ],     [         'title' =&gt; '这是一篇测试文章333',         'content' =&gt; '测试内容'     ], ]; $post-&gt;batchInsert($items); $items = $post-&gt;selectAll(); print_r($items);</pre> <p>执行这段代码，打印结果中包含新插入的文章数据，则表明事务提交成功：</p> <p><img src="https://img.php.cn/upload/article/000/000/052/c1325dcafab6f21a191cd23114f0f785-2.jpg" alt="Interact with MySQL database through PDO extension to implement addition, deletion, modification and query implementation and database transactions"></p> <h3>小结</h3> <p>关于通过 PDO 扩展与 MySQL 数据库交互，我们就简单介绍到这里，更多细节可以阅读官方文档，相信通过这几个课程的学习，你已经对 MySQL 数据库的基本使用以及如何在 PHP 中连接数据库并进行增删改查有了初步的认知，从下篇教程开始，我们将结合具体实战项目来开发一个现代的 PHP 项目，将之前的学习到的知识点应用到实战中，并且引入一些现代的 PHP 理念对项目进行管理。</p> <blockquote><p>想了解更多相关文章，敬请关注<a href="https://www.php.cn/topic/php-mysql/" target="_blank">php mysql</a>栏目！<br></p></blockquote>

The above is the detailed content of Interact with MySQL database through PDO extension to implement addition, deletion, modification and query implementation and database transactions. For more information, please follow other related articles on the PHP Chinese website!