Interacting with MySQL database through PDO extension to realize adding, deleting, modifying and querying and database transactions

Related learning recommendation: mysql tutorial

Add, delete, modify and query through prepared statements

Why use prepared statements

We have briefly introduced the prepared statement in the previous tutorial. We can compare it with the view template. The so-called prepared statement is a predefined SQL statement template, and the specific parameter values ​​​​are replaced by placeholders. :

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

Then bind the specific parameter value with the corresponding placeholder through a specific API method before actually executing the SQL statement and mapping. It’s as if the defined view template also replaces the variable with a specific placeholder, and then passes the variable value in to fill and render when it is actually rendered.

Why take all this trouble? Wouldn’t it be nice to directly use the query method demonstrated earlier to add, delete, modify, and check? Uh, then let’s talk about the benefits of prepared statements, or why use prepared statements for database interaction. There are two advantages:

• First, use prepared statements to define in advance The SQL template will only be parsed once, but it 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;
• Secondly, post-transfer Parameter values ​​for prepared statements will be processed by the underlying driver, thus effectively avoiding SQL injection attacks.

In summary, from the perspective of performance and security, it is recommended to use prepared statements to handle database addition, deletion, modification and query operations.

Example code for addition, deletion, modification, and query

Next, we will implement the operation of adding, deleting, modifying, and checking the MySQL database based on the prepared statement API provided by PDO, and 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 {
             // prepare prepared statement
             $stmt = $this->pdo->prepare($sql);
             // Get the format string corresponding to the current time: 2020-05-28 13:00:00
             $datetime = date('Y-m-d H:i:s', time());
             // bind parameter value
             $stmt->bindParam(':title', $title, PDO::PARAM_STR);
             $stmt->bindParam(':content', $content, PDO::PARAM_STR);
             $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
             // execute statement
             $stmt->execute();
             return $this->pdo->lastInsertId(); // Return the corresponding ID of the inserted record
         } catch (PDOException $e) {
             printf("Database insert failed: %s\n", $e->getMessage());
         }
     }

     public function select($id)
     {
         $sql = 'SELECT * FROM `post` WHERE id = ?';
         try {
             // prepare prepared statement
             $stmt = $this->pdo->prepare($sql);
             // bind parameter value
             $stmt->bindValue(1, $id, PDO::PARAM_INT);
             // execute statement
             $stmt->execute();
             return $stmt->fetchObject(self::class); // Return the result set as an object
         } catch (PDOException $e) {
             printf("Database query failed: %s\n", $e->getMessage());
         }
     }

     public function selectAll()
     {
         $sql = 'SELECT * FROM `post` ORDER BY id DESC';
         try {
             // prepare prepared statement
             $stmt = $this->pdo->prepare($sql);
             // execute statement
             $stmt->execute();
             return $stmt->fetchAll(); // return all result sets
         } catch (PDOException $e) {
             printf("Database query failed: %s\n", $e->getMessage());
         }
     }

     public function update($id)
     {
         $sql = 'UPDATE `post` SET created_at = :created_at WHERE id = :id';
         try {
             // prepare prepared statement
             $stmt = $this->pdo->prepare($sql);
             $datetime = date('Y-m-d H:i:s', time());
             // bind parameter value
             $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
             $stmt->bindValue(':id', $id, PDO::PARAM_INT);
             // execute statement
             $stmt->execute();
             return $stmt->rowCount();
         } catch (PDOException $e) {
             printf("Database update failed: %s\n", $e->getMessage());
         }
     }

     public function delete($id)
     {
         $sql = 'DELETE FROM `post` WHERE id = ?';
         try {
             // prepare prepared statement
             $stmt = $this->pdo->prepare($sql);
             // bind parameter value
             $stmt->bindValue(1, $id, PDO::PARAM_INT);
             // execute statement
             $stmt->execute();
             return $stmt->rowCount();
         } catch (PDOException $e) {
             printf("Database deletion failed: %s\n", $e->getMessage());
         }
     }
 }

We built a Post class, and then initialized the $pdo instance (passed in from the outside) in the constructor, and then based on the preprocessing The addition, deletion, modification, and query operations implemented by the statement are decomposed into the corresponding class methods. The overall logic is very simple. Taking insert as an example, first pass the prepare method of the PDO object into the SQL template to construct the prepared statement, which returns the PDOStatement object, and then calls the bindParam method of the object Bind the specific parameter value. The first parameter of this method is a placeholder, the second parameter is the parameter value, and the third parameter is the value type (the corresponding constant can be queried in the PDO predefined constant), and the binding is done. After the parameters, you can call the execute method of the PDOStatement object to execute the prepared statement.

For the insert operation, the lastInsertId method on the PDO object can return the primary key ID of the inserted record. For the update and delete methods, the rowCount method on the PDOStatement object can return the number of affected rows to indicate whether the operation is successful. For query operations, a single record can be returned through the fetch method of the PDOStatement object, or an object instance (also a single record) mapped to a specified class 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 a prepared statement, you can pass the ? placeholder, or you can pass the readable :name Placeholders with better performance, and then when binding parameters, you can use the bindValue or bindParam method, the parameters are the same for both, but for ? placeholders, you need to use the numerical serial number to establish Mappings to SQL templates (1-based).

It is not difficult to understand the above code by combining the code and the official PHP document. Next, let’s write the test code:

//  Initialize the PDO connection instance
 $dsn = 'mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8mb4';
 $user = & # 39; root & # 39;;
 $pass = & # 39; root & # 39;;
 try {
     $pdo = new PDO($dsn, $user, $pass);
 } catch (PDOException $e) {
     printf("Database connection failed: %s\n", $e->getMessage());
 }

 // test code
 $post = new Post($pdo);
 // insert
 $title = & # 39; This is a test article & # 39;;
 $cOntent= & # 39; Test content: The weather is good today & # 39;;
 $id = $post->insert($title, $content);
 echo & # 39; Article inserted successfully: & # 39; . $id . & # 39; 
 & # 39;;
 // select
 $item = $post->select($id);
 echo '
';
 print_r($item);
 // update
 $affected = $post->update($id);
 echo 'Number of affected lines: ' . $affected . '
';
 //delete
 $affected = $post->delete($id);
 echo 'Number of affected lines: ' . $affected . '
';
 // selectAll
 $items = $post->selectAll();
 print_r($items);
Initialize a PDO object instance and pass it into the Post constructor, and then call the method of adding, deleting, modifying and checking the Post object in turn.  Access in the browser, and the printed results are as follows:

For more mode settings, please refer to the introduction and examples of the fetchAll method in the official document.  
Database transaction
Finally, let’s take a look at how to implement database transaction commit and rollback through PDO extension. We already know that for a single SQL statement, transaction commit and rollback  Rolling is done automatically. For SQL statement sequences (multiple SQL statements), you need to explicitly start and commit transactions. PDO objects also provide corresponding API methods for this.  Very simple, for example, we add a batch insert method batchInsert method in the Post class:
  public function batchInsert(array $items)
 {
     $sql = 'INSERT INTO `post` (title, content, created_at) VALUES (:title, :content, :created_at)';
     try {
         // start transaction
         $this->pdo->beginTransaction();
         // prepare prepared statement
         $stmt = $this->pdo->prepare($sql);
         foreach ($items as $item) {
             // bind parameter value
             $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);
             // execute statement
             $stmt->execute();
         }
         $this->pdo->commit(); // commit transaction
         return $stmt->rowCount(); // returns the number of affected rows
     } catch (PDOException $e) {
         $this->pdo->rollBack(); // rollback transaction
         printf("Database batch insert failed: %s\n", $e->getMessage());
     }
 }
We only need to call the beginTransaction method of the PDO object to start the transaction before executing the SQL sequence, and then call the commit method to commit the transaction after all SQL statements are executed.  In the code, the transaction is rolled back through the rollBack method of the PDO object.  
Write test code for the above method:
$post = new Post($pdo);
 $items = [
     [
         & # 39; title & # 39; => & # 39; This is a test article 111 & # 39;,
         'content' => 'test content'
     ],
     [
         & # 39; title & # 39; => & # 39; This is a test article 222 & # 39;,
         'content' => 'test content'
     ],
     [
         & # 39; title & # 39; => & # 39; This is a test article 333 & # 39;,
         'content' => 'test content'
     ],
 ];
 $post->batchInsert($items);
 $items = $post->selectAll();
 print_r($items);
Execute this code, if the print result contains newly inserted article data, it means that the transaction is submitted successfully:


 The above is the detailed content of adding, deleting, modifying and querying and database transactions through the interaction between PDO extension and MySQL database. For more information, please pay attention to other related articles on 1024programmer.com!  
��, if an error occurs during SQL execution, the transaction is rolled back through the rollBack method of the PDO object in the exception handling code.  
Write test code for the above method:
$post = new Post($pdo);
 $items = [
     [
         & # 39; title & # 39; => & # 39; This is a test article 111 & # 39;,
         'content' => 'test content'
     ],
     [
         & # 39; title & # 39; => & # 39; This is a test article 222 & # 39;,
         'content' => 'test content'
     ],
     [
         & # 39; title & # 39; => & # 39; This is a test article 333 & # 39;,
         'content' => 'test content'
     ],
 ];
 $post->batchInsert($items);
 $items = $post->selectAll();
 print_r($items);
Execute this code, if the print result contains newly inserted article data, it means that the transaction is submitted successfully:


 The above is the detailed content of adding, deleting, modifying and querying and database transactions through the interaction between PDO extension and MySQL database. For more information, please pay attention to other related articles on 1024programmer.com!