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 thequerymethod 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:

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

class, and then initialized the$pdoinstance 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. Takeinsertas 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 . '
'; // select $item = $post->select($id); echo '
'; print_r($item); // update $affected = $post->update($id); echo '受影响的行数: ' . $affected . '
'; // delete $affected = $post->delete($id); echo '受影响的行数: ' . $affected . '
'; // selectAll $items = $post->selectAll(); print_r($items);
        

         Copy after login
        
       

Initialize a PDO object instance and pass it in

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

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 thePostobject array, you can do this:

return $stmt->fetchAll(PDO::FETCH_CLASS, self::class);

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

class.batchInsertmethod:

public function batchInsert(array $items) { $sql = 'INSERT INTO `post` (title, content, created_at) VALUES (:title, :content, :created_at)'; try { // 开启事务 $this->pdo->beginTransaction(); // 准备预处理语句 $stmt = $this->pdo->prepare($sql); foreach ($items as $item) { // 绑定参数值 $datetime = date('Y-m-d H:i:s', time()); $stmt->bindParam(':title', $item->title, PDO::PARAM_STR); $stmt->bindParam(':content', $item->content, PDO::PARAM_STR); $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR); // 执行语句 $stmt->execute(); } $this->pdo->commit(); // 提交事务 return $stmt->rowCount(); // 返回受影响的行数 } catch (PDOException $e) { $this->pdo->rollBack(); // 回滚事务 printf("数据库批量插入失败: %s\n", $e->getMessage()); } }

$post = new Post($pdo); $items = [ [ 'title' => '这是一篇测试文章111', 'content' => '测试内容' ], [ 'title' => '这是一篇测试文章222', 'content' => '测试内容' ], [ 'title' => '这是一篇测试文章333', 'content' => '测试内容' ], ]; $post->batchInsert($items); $items = $post->selectAll(); print_r($items);

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!