mysql pdo.php_如何使用PDO PHP扩展在Ubuntu 18.04上PHP中执行MySQL事务

最新推荐文章于 2023-04-23 18:16:08 发布

cukw6666

最新推荐文章于 2023-04-23 18:16:08 发布

阅读量293

点赞数

文章标签：数据库 python mysql php java

原文链接：https://www.digitalocean.com/community/tutorials/how-to-use-the-pdo-php-extension-to-perform-mysql-transactions-in-php-on-ubuntu-18-04

版权

mysql pdo.php

The author selected Open Sourcing Mental Illness to receive a donation as part of the Write for DOnations program.

作者选择了开放源性精神疾病作为Write for DOnations计划的一部分接受捐赠。

介绍 (Introduction)

A MySQL transaction is a group of logically related SQL commands that are executed in the database as a single unit. Transactions are used to enforce ACID (Atomicity, Consistency, Isolation, and Durability) compliance in an application. This is a set of standards that govern the reliability of processing operations in a database.

MySQL事务是在数据库中作为单个单元执行的一组逻辑相关SQL命令。事务用于在应用程序中强制执行ACID (原子性，一致性，隔离性和持久性)合规性。这是一组标准，用于管理数据库中处理操作的可靠性。

Atomicity ensures the success of related transactions or a complete failure if an error occurs. Consistency guarantees the validity of the data submitted to the database according to defined business logic. Isolation is the correct execution of concurrent transactions ensuring the effects of different clients connecting to a database do not affect each other. Durability ensures that logically related transactions remain in the database permanently.

原子性确保相关交易成功或发生错误时完全失败。一致性保证了根据定义的业务逻辑提交给数据库的数据的有效性。隔离是并发事务的正确执行，可确保连接到数据库的不同客户端的影响不会相互影响。持久性可确保与逻辑相关的事务永久保留在数据库中。

SQL statements issued via a transaction should either succeed or fail altogether. If any of the queries fails, MySQL rolls back the changes and they are never committed to the database.

通过事务发出SQL语句应该完全成功或失败。如果任何查询失败，MySQL将回滚更改，并且永远不会将其提交给数据库。

A good example to understand how MySQL transactions work is an e-commerce website. When a customer makes an order, the application inserts records into several tables, such as: orders and orders_products, depending on the business logic. Multi-table records related to a single order must be atomically sent to the database as a single logical unit.

电子商务网站是了解MySQL交易方式的一个很好的例子。当客户下订单时，应用程序会根据业务逻辑将记录插入到多个表中，例如： orders和orders_products 。与单个订单相关的多表记录必须作为单个逻辑单元原子地发送到数据库。

Another use-case is in a bank application. When a client is transferring money, a couple of transactions are sent to the database. The sender’s account is debited and the receiver’s party account is credited. The two transactions must be committed simultaneously. If one of them fails, the database will revert to its original state and no changes should be saved to disk.

另一个用例是在银行应用程序中。当客户转移资金时，几笔交易会发送到数据库。发送方的帐户被记入借方，而接收方的一方帐户被记入贷方。这两个事务必须同时提交。如果其中之一失败，数据库将恢复为原始状态，并且不应将任何更改保存到磁盘。

In this tutorial, you will use the PDO PHP Extension, which provides an interface for working with databases in PHP, to perform MySQL transactions on an Ubuntu 18.04 server.

在本教程中，您将使用PDO PHP扩展，它提供了使用PHP中的数据库的接口，以在Ubuntu 18.04服务器上执行MySQL事务。

先决条件 (Prerequisites)

Before you begin, you will need the following:

在开始之前，您需要满足以下条件：

One Ubuntu 18.04 server set up by following the Initial Server Setup with Ubuntu 18.04, including a sudo non-root user.
通过对Ubuntu 18.04进行初始服务器设置来设置一台Ubuntu 18.04服务器，包括sudo非root用户。
Apache, MySQL, and PHP installed on your system. You can follow the guide on How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 18.04. You can skip Step 4 (setting up virtual hosts) and work directly with the default Apache settings.
系统上安装了Apache，MySQL和PHP。您可以按照有关如何在Ubuntu 18.04上安装Linux，Apache，MySQL，PHP(LAMP)堆栈的指南进行操作。您可以跳过第4步(设置虚拟主机)，并直接使用默认的Apache设置。

第1步-创建示例数据库和表 (Step 1 — Creating a Sample Database and Tables)

You’ll first create a sample database and add some tables before you start working with MySQL transactions. First, log in to your MySQL server as root:

在开始使用MySQL事务之前，您将首先创建一个示例数据库并添加一些表。首先，以root用户身份登录到MySQL服务器：

sudo mysql -u root -p
须藤mysql -u root -p

When prompted, enter your MySQL root password and hit ENTER to proceed. Then, create a database, for the purposes of this tutorial we’ll call the database sample_store:

出现提示时，输入您MySQL根密码，然后按ENTER继续。然后，创建一个数据库，就本教程而言，我们将其称为sample_store数据库：

CREATE DATABASE sample_store;
创建数据库sample_store ;

You will see the following output:

您将看到以下输出：


   
   
    
    Output
   
   Query OK, 1 row affected (0.00 sec)

Create a user called sample_user for your database. Remember to replace PASSWORD with a strong value:

为您的数据库创建一个名为sample_user的用户。切记用一个强大的值替换PASSWORD ：

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';
创建用户' sample_user '@'localhost'由' 密码 '标识;

Issue full privileges for your user to the sample_store database:

向您的用户授予sample_store数据库的完整特权：

GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';
将所有特权授予 sample_store 。*至' sample_user '@'localhost';

Finally, reload the MySQL privileges:

最后，重新加载MySQL特权：

FLUSH PRIVILEGES;
冲洗特权;

You’ll see the following output once you’ve created your user:

创建用户后，您将看到以下输出：


   
   
    
    Output
   
   Query OK, 0 rows affected (0.01 sec)
. . .

With the database and user in place, you can now create several tables for demonstrating how MySQL transactions work.

在数据库和用户就位之后，您现在可以创建几个表来演示MySQL事务的工作方式。

Log out from the MySQL server:

从MySQL服务器注销：

QUIT;
退出;

Once the system logs you out, you will see the following output:

系统注销后，您将看到以下输出：


   
   
    
    Output
   
   Bye.

Then, log in with the credentials of the sample_user you just created:

然后，使用您刚创建的sample_user的凭据登录：

sudo mysql -u sample_user -p
须藤mysql -u sample_user -p

Enter the password for the sample_user and hit ENTER to proceed.

输入sample_user的密码，然后sample_user ENTER继续。

Switch to the sample_store to make it the currently selected database:

切换到sample_store以使其成为当前选定的数据库：

USE sample_store;
USE sample_store ;

You’ll see the following output once it is selected:

选择后，您将看到以下输出：


   
   
    
    Output
   
   Database Changed.

Next, create a products table:

接下来，创建一个products表：

CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;
创建表产品 ( product_id BIGINT主键AUTO_INCREMENT， product_name VARCHAR(50)，价格 DOUBLE)ENGINE = InnoDB;

This command creates a products table with a field named product_id. You use a BIGINT data type that can accommodate a large value of up to 2^63-1. You use this same field as a PRIMARY KEY to uniquely identify products. The AUTO_INCREMENT keyword instructs MySQL to generate the next numeric value as new products are inserted.

此命令将创建一个名为product_id的products表。您使用的BIGINT数据类型最多可容纳2 ^ 63-1的较大值。您将同一字段用PRIMARY KEY来唯一标识产品。 AUTO_INCREMENT关键字指示MySQL在插入新产品时生成下一个数值。

The product_name field is of type VARCHAR that can hold up to a maximum of 50 letters or numbers. For the product price, you use a DOUBLE data type to cater for floating point formats in prices with decimal numbers.

product_name字段的类型为VARCHAR ，最多可容纳50字母或数字。对于产品price ，您可以使用DOUBLE数据类型来满足带有十进制数字的价格中的浮点格式。

Lastly, you use the InnoDB as the ENGINE because it comfortably supports MySQL transactions as opposed to other storage engines such as MyISAM.

最后，您将InnoDB用作ENGINE因为它轻松地支持MySQL事务，而不是MyISAM等其他存储引擎。

Once you’ve created your products table, you’ll get the following output:

创建products表后，将获得以下输出：


   
   
    
    Output
   
   Query OK, 0 rows affected (0.02 sec)

Next, add some items to the products table by running the following commands:

接下来，通过运行以下命令将一些项目添加到products表：

INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
插入产品 (产品名称，价格)值(“冬衣”，“ 25.50”)；
INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
将产品 (产品名称，价格)插入值(“刺绣衬衫”，“ 13.90”)；
INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
插入产品 (产品名称，价格)值(“时尚鞋”，“ 45.30”)；
INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');
插入产品 (产品名称，价格)值(“ PROXIMA TROUSER”，“ 39.95”)；

You’ll see output similar to the following after each INSERT operation:

在每个INSERT操作之后，您将看到类似于以下的输出：


   
   
    
    Output
   
   Query OK, 1 row affected (0.02 sec)
. . .

Then, verify that the data was added to the products table:

然后，验证数据已添加到产品表中：

SELECT * FROM products;
选择*从产品 ;

You will see a list of the four products that you have inserted:

您将看到已插入的四个产品的列表：


   
   
    
    Output
   
   +------------+-------------------+-------+
| product_id | product_name      | price |
+------------+-------------------+-------+
|          1 | WINTER COAT       |  25.5 |
|          2 | EMBROIDERED SHIRT |  13.9 |
|          3 | FASHION SHOES     |  45.3 |
|          4 | PROXIMA TROUSER   | 39.95 |
+------------+-------------------+-------+
4 rows in set (0.01 sec)

Next, you’ll create a customers table for holding basic information about customers:

接下来，您将创建一个customers表，用于保存有关客户的基本信息：

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;
创建表客户 ( customer_id BIGINT主键AUTO_INCREMENT， customer_name VARCHAR(50))ENGINE = InnoDB;

As in the products table, you use the BIGINT data type for the customer_id and this will ensure the table can support a lot of customers up to 2^63-1 records. The keyword AUTO_INCREMENT increments the value of the columns once you insert a new customer.

像在products表中一样，将BIGINT数据类型用于customer_id ，这将确保该表可以支持很多客户，最多2 ^ 63-1个记录。一旦插入新客户，关键字AUTO_INCREMENT增加列的值。

Since the customer_name column accepts alphanumeric values, you use VARCHAR data type with a limit of 50 characters. Again, you use the InnoDB storage ENGINE to support transactions.

由于customer_name列接受字母数字值，因此您使用的VARCHAR数据类型限制为50字符。同样，您使用InnoDB存储ENGINE来支持事务。

After running the previous command to create the customers table, you will see the following output:

运行上一条命令创建customers表后，您将看到以下输出：


   
   
    
    Output
   
   Query OK, 0 rows affected (0.02 sec)

You’ll add three sample customers to the table. Run the following commands:

您将在表中添加三个样本客户。运行以下命令：

INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
插入客户(customer_name)值('JOHN DOE');
INSERT INTO customers(customer_name) VALUES ('ROE MARY');
INSERT INTO customer(customer_name)VALUES('ROE MARY');
INSERT INTO customers(customer_name) VALUES ('DOE JANE');
插入客户(customer_name)值('DOE JANE');

Once the customers have been added, you will see an output similar to the following:

添加客户之后，您将看到类似于以下内容的输出：


   
   
    
    Output
   
   Query OK, 1 row affected (0.02 sec)
. . .

Then, verify the data in the customers table:

然后，验证customers表中的数据：

SELECT * FROM customers;
选择*从客户 ;

You’ll see a list of the three customers:

您将看到三个客户的列表：


   
   
    
    Output
   
   +-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JOHN DOE      |
|           2 | ROE MARY      |
|           3 | DOE JANE      |
+-------------+---------------+
3 rows in set (0.00 sec)

Next, you’ll create an orders table for recording orders placed by different customers. To create the orders table, execute the following command:

接下来，您将创建一个orders表以记录不同客户下的订单。要创建orders表，执行以下命令：

CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;
创建表订单 ( order_id BIGINT AUTO_INCREMENT主键， order_date DATETIME， customer_id BIGINT， order_total DOUBLE)ENGINE = InnoDB;

You use the column order_id as the PRIMARY KEY. The BIGINT data type allows you to accommodate up to 2^63-1 orders and will auto-increment after each order insertion. The order_date field will hold the actual date and time the order is placed and hence, you use the DATETIME data type. The customer_id relates to the customers table that you created previously.

您将order_id列用作PRIMARY KEY 。 BIGINT数据类型允许您容纳最多2 ^ 63-1个订单，并且在每次插入订单后都会自动递增。 order_date字段将保存下订单的实际日期和时间，因此，您使用DATETIME数据类型。 customer_id与您先前创建的customers表相关。

You will see the following output:

您将看到以下输出：


   
   
    
    Output
   
   Query OK, 0 rows affected (0.02 sec)

Since a single customer’s order may contain multiple items, you need to create an orders_products table to hold this information.

由于单个客户的订单可能包含多个项目，因此您需要创建一个orders_products表来保存此信息。

To create the orders_products table, run the following command:

要创建orders_products表，请运行以下命令：

CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;
创建表orders_products ( ref_id BIGINT主键AUTO_INCREMENT， order_id BIGINT， product_id BIGINT，价格DOUBLE，数量BIGINT)ENGINE = InnoDB;

You use the ref_id as the PRIMARY KEY and this will auto-increment after each record insertion. The order_id and product_id relate to the orders and the products tables respectively. The price column is of data type DOUBLE in order to accommodate floating values.

您将ref_id用作PRIMARY KEY并且在每次插入记录后都会自动递增。 order_id和product_id与orders和products表有关。为了容纳浮动值， price列的数据类型为DOUBLE 。

The storage engine InnoDB must match the other tables created previously since a single customer’s order will affect multiple tables simultaneously using transactions.

存储引擎InnoDB必须与先前创建的其他表匹配，因为单个客户的订单将同时使用事务影响多个表。

Your output will confirm the table’s creation:

您的输出将确认表的创建：


   
   
    
    Output
   
   Query OK, 0 rows affected (0.02 sec)

You won’t be adding any data to the orders and orders_products tables for now but you’ll do this later using a PHP script that implements MySQL transactions.

您暂时不会在orders和orders_products表中添加任何数据，但是稍后将使用实现MySQL事务PHP脚本进行添加。

Log out from the MySQL server:

从MySQL服务器注销：

QUIT;
退出;

Your database schema is now complete and you’ve populated it with some records. You’ll now create a PHP class for handling database connections and MySQL transactions.

现在，您的数据库架构已经完成，并且已经在其中填充了一些记录。现在，您将创建一个PHP类来处理数据库连接和MySQL事务。

第2步-设计一个PHP类来处理MySQL事务 (Step 2 — Designing a PHP Class to Handle MySQL Transactions)

In this step, you will create a PHP class that will use PDO (PHP Data Objects) to handle MySQL transactions. The class will connect to your MySQL database and insert data atomically to the database.

在此步骤中，您将创建一个PHP类，该类将使用PDO(PHP数据对象)来处理MySQL事务。该类将连接到您MySQL数据库，并自动向数据库中插入数据。

Save the class file in the root directory of your Apache web server. To do this, create a DBTransaction.php file using your text editor:

将类文件保存在Apache Web服务器的根目录中。为此，使用文本编辑器创建一个DBTransaction.php文件：

sudo nano /var/www/html/DBTransaction.php
须藤纳米/var/www/html/DBTransaction.php

Then, add the following code to the file. Replace PASSWORD with the value you created in Step 1:

然后，将以下代码添加到文件中。将PASSWORD替换为您在步骤1中创建的值：

/var/www/html/DBTransaction.php

<?php

class DBTransaction
{
    protected $pdo;
    public $last_insert_id;

    public function __construct()
    {
        define('DB_NAME', 'sample_store');
        define('DB_USER', 'sample_user');
        define('DB_PASSWORD', 'PASSWORD');
        define('DB_HOST', 'localhost');

        $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

Toward the beginning of the DBTransaction class, the PDO will use the constants (DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD) to initialize and connect to the database that you created in step 1.

在DBTransaction类的开头，PDO将使用常量( DB_HOST ， DB_NAME ， DB_USER和DB_PASSWORD )初始化并连接到您在步骤1中创建的数据库。

Note: Since we are demonstrating MySQL transactions in a small scale here, we have declared the database variables in the DBTransaction class. In a large production project, you would normally create a separate configuration file and load the database constants from that file using a PHP require_once statement.

注意：由于这里是小规模MySQL事务演示，因此我们在DBTransaction类中声明了数据库变量。在大型生产项目中，通常会创建一个单独的配置文件，并使用PHP require_once语句从该文件加载数据库常量。

Next, you set two attributes for the PDO class:

接下来，为PDO类设置两个属性：

ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: This attribute instructs PDO to throw an exception if an error is encountered. Such errors can be logged for debugging.
ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ：如果遇到错误，此属性指示PDO引发异常。可以记录此类错误以进行调试。
ATTR_EMULATE_PREPARES, false: This option disables emulation of prepared statements and allows the MySQL database engine to prepare the statements itself.
ATTR_EMULATE_PREPARES, false ：此选项禁用对准备好的语句的仿真，并允许MySQL数据库引擎自行准备语句。

Now add the following code to your file to create the methods for your class:

现在，将以下代码添加到文件中，以创建您的类的方法：

/var/www/html/DBTransaction.php

. . .
    public function startTransaction()
    {
        $this->pdo->beginTransaction();
    }

    public function insertTransaction($sql, $data)
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        $this->last_insert_id = $this->pdo->lastInsertId();
    }

    public function submitTransaction()
    {
        try {
            $this->pdo->commit();
        } catch(PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }

          return true;
    }
}

Save and close the file by pressing CTRL + X, Y, then ENTER.

通过按CTRL + X ， Y ，然后按ENTER保存并关闭文件。

To work with MySQL transactions, you create three main methods in the DBTransaction class; startTransaction, insertTransaction, and submitTransaction.

要使用MySQL事务，您需要在DBTransaction类中创建三个主要方法。 startTransaction ， insertTransaction和submitTransaction 。

startTransaction: This method instructs PDO to start a transaction and turns auto-commit off until a commit command is issued.
startTransaction ：此方法指示PDO启动事务并关闭自动提交，直到发出提交命令为止。
insertTransaction : This method takes two arguments. The $sql variable holds the SQL statement to be executed while the $data variable is an array of the data to be bound to the SQL statement since you’re using prepared statements. The data is passed as an array to the insertTransaction method.
insertTransaction ：此方法insertTransaction两个参数。 $sql变量保存要执行SQL语句，而$data变量是要绑定到SQL语句的数据的数组，因为您使用的是准备好的语句。数据作为数组传递给insertTransaction方法。
submitTransaction : This method commits the changes to the database permanently by issuing a commit() command. However, if there is an error and the transactions have a problem, the method calls the rollBack() method to revert the database to its original state in case a PDO exception is raised.
submitTransaction ：此方法通过发出commit()命令将更改永久提交到数据库。但是，如果出现错误且事务有问题，该方法将调用rollBack()方法，以在引发PDO异常的情况下将数据库还原到其原始状态。

Your DBTransaction class initializes a transaction, prepares the different SQL commands to be executed, and finally commits the changes to the database atomically if there are no issues, otherwise, the transaction is rolled back. In addition, the class allows you to retrieve the record order_id you just created by accessing the public property last_insert_id.

您的DBTransaction类初始化一个事务，准备要执行的不同SQL命令，最后在没有问题的情况下最终将更改自动提交给数据库，否则，该事务将回滚。此外，该类还允许您通过访问公共属性last_insert_id检索刚刚创建的记录order_id 。

The DBTransaction class is now ready to be called and used by any PHP code, which you’ll create next.

DBTransaction类现在可以被任何PHP代码调用和使用了，接下来将创建它们。

步骤3 —创建一个PHP脚本以使用DBTransaction类 (Step 3 — Creating a PHP Script to Use the DBTransaction Class)

You’ll create a PHP script that will implement the DBTransaction class and send a group of SQL commands to the MySQL database. You’ll mimic the workflow of a customer’s order in an online shopping cart.

您将创建一个PHP脚本，该脚本将实现DBTransaction类，并将一组SQL命令发送到MySQL数据库。您将在在线购物车中模拟客户订单的工作流程。

These SQL queries will affect the orders and the orders_products tables. Your DBTransaction class should only allow changes to the database if all of the queries are executed without any errors. Otherwise, you’ll get an error back and any attempted changes will roll back.

这些SQL查询将影响orders和orders_products表。如果执行了所有查询而没有任何错误，则DBTransaction类应仅允许对数据库进行更改。否则，您将返回错误，并且任何尝试的更改都将回滚。

You are creating a single order for the customer JOHN DOE identified with customer_id 1. The customer’s order has three different items with differing quantities from the products table. Your PHP script takes the customer’s order data and submits it into the DBTransaction class.

您正在为标识为customer_id 1的客户JOHN DOE创建单个订单。客户的订单中有三个与products表中数量不同的不同项目。您PHP脚本获取客户的订单数据，并将其提交到DBTransaction类中。

Create the orders.php file:

创建orders.php文件：

sudo nano /var/www/html/orders.php
须藤纳米/var/www/html/orders.php

Then, add the following code to the file:

然后，将以下代码添加到文件中：

/var/www/html/orders.php

<?php

require("DBTransaction.php");

$db_host = "database_host";
$db_name = "database_name";
$db_user = "database_user";
$db_password = "PASSWORD";

$customer_id = 2;

$products[] = [
  'product_id' => 1,
  'price' => 25.50,
  'quantity' => 1
];

$products[] = [
  'product_id' => 2,
  'price' => 13.90,
  'quantity' => 3
];

$products[] = [
  'product_id' => 3,
  'price' => 45.30,
  'quantity' => 2
];

$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

You’ve created a PHP script that initializes an instance of the DBTransaction class that you created in Step 2.

您已经创建了一个PHP脚本，用于初始化在步骤2中创建的DBTransaction类的实例。

In this script, you include the DBTransaction.php file and you initialize the DBTransaction class. Next, you prepare a multi-dimensional array of all the products the customer is ordering from the store. You also invoke the startTransaction() method to start a transaction.

在此脚本中，包括DBTransaction.php文件，并初始化DBTransaction类。接下来，您准备一个多维数组，其中包含客户从商店订购的所有产品。您还可以调用startTransaction()方法来启动事务。

Next add the following code to finish your orders.php script:

接下来添加以下代码以完成您的orders.php脚本：

/var/www/html/orders.php

. . .
$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";

$transaction->insertQuery($order_query, [
  'customer_id' => $customer_id,
  'order_date' => "2020-01-11",
  'order_total' => 157.8
]);

$order_id = $transaction->last_insert_id;

foreach ($products as $product) {
  $transaction->insertQuery($product_query, [
    'order_id' => $order_id,
    'product_id' => $product['product_id'],
    'price' => $product['price'],
    'quantity' => $product['quantity']
  ]);
}

$result = $transaction->submit();

if ($result) {
    echo "Records successfully submitted";
} else {
    echo "There was an error.";
}

Save and close the file by pressing CTRL + X, Y, then ENTER.

通过按CTRL + X ， Y ，然后按ENTER保存并关闭文件。

You prepare the command to be inserted to the orders table via the insertTransaction method. After this, you retrieve the value of the public property last_insert_id from the DBTransaction class and use it as the $order_id.

您准备通过insertTransaction方法将命令插入订单表。此后，您从DBTransaction类检索公共属性last_insert_id的值，并将其用作$order_id 。

Once you have an $order_id, you use the unique ID to insert the customer’s order items to the orders_products table.

一旦有了$order_id ，就可以使用唯一ID将客户的订单商品插入orders_products表。

Finally, you call the method submitTransaction to commit the entire customer’s order details to the database if there are no problems. Otherwise, the method submitTransaction will rollback the attempted changes.

最后，如果没有问题，则调用方法submitTransaction将整个客户的订单详细信息提交到数据库。否则，方法submitTransaction将回滚尝试的更改。

Now you’ll run the orders.php script in your browser. Run the following and replace your-server-IP with the public IP address of your server:

现在，您将在浏览器中运行orders.php脚本。运行以下命令，并将your-server-IP替换your-server-IP的公共IP地址：

http://your-server-IP/orders.php

http:// your-server-IP /orders.php

You will see confirmation that the records were successfully submitted:

您将看到确认记录已成功提交的确认：

Your PHP script is working as expected and the order together with the associated order products were submitted to the database atomically.

您PHP脚本正在按预期方式工作，并且该订单及其关联的订单产品已自动提交到数据库。

You’ve run the orders.php file on a browser window. The script invoked the DBTransaction class which in turn submitted the orders details to the database. In the next step, you will verify if the records saved to the related database tables.

您已经在浏览器窗口中运行了orders.php文件。该脚本调用了DBTransaction类，该类又将orders详细信息提交给数据库。在下一步中，您将验证记录是否保存到相关的数据库表中。

步骤4 —确认数据库中的条目 (Step 4 — Confirming the Entries in Your Database)

In this step, you’ll check if the transaction initiated from the browser window for the customer’s order was posted to the database tables as expected.

在此步骤中，您将检查从浏览器窗口发起的客户订单交易是否已按预期发布到数据库表中。

To do this, log in to your MySQL database again:

为此，请再次登录到您MySQL数据库：

sudo mysql -u sample_user -p
须藤mysql -u sample_user -p

Enter the password for the sample_user and hit ENTER to continue.

输入sample_user的密码，然后sample_user ENTER继续。

Switch to the sample_store database:

切换到sample_store数据库：

USE sample_store;
使用sample_store ;

Ensure the database is changed before proceeding by confirming the following output:

通过确认以下输出，确保在继续之前更改数据库：


   
   
    
    Output
   
   Database Changed.

Then, issue the following command to retrieve records from the orders table:

然后，发出以下命令以从orders表中检索记录：

SELECT * FROM orders;
选择*从订单 ;

This will display the following output detailing the customer’s order:

这将显示以下输出，详细说明客户的订单：


   
   
    
    Output
   
   +----------+---------------------+-------------+-------------+
| order_id | order_date          | customer_id | order_total |
+----------+---------------------+-------------+-------------+
|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |
+----------+---------------------+-------------+-------------+
1 row in set (0.00 sec)

Next, retrieve the records from the orders_products table:

接下来，从orders_products表中检索记录：

SELECT * FROM orders_products;
选择* FROMorders_products ;

You’ll see output similar to the following with a list of products from the customer’s order:

您将看到类似于以下内容的输出，其中包含来自客户订单的产品列表：


   
   
    
    Output
   
   +--------+----------+------------+-------+----------+
| ref_id | order_id | product_id | price | quantity |
+--------+----------+------------+-------+----------+
|      1 |  1       |          1 |  25.5 |        1 |
|      2 |  1       |          2 |  13.9 |        3 |
|      3 |  1       |          3 |  45.3 |        2 |
+--------+----------+------------+-------+----------+
3 rows in set (0.00 sec)

The output confirms that the transaction was saved to the database and your helper DBTransaction class is working as expected.

输出确认该事务已保存到数据库，并且您的助手DBTransaction类正在按预期工作。

结论 (Conclusion)

In this guide, you used the PHP PDO to work with MySQL transactions. Although this is not a conclusive article on designing an e-commerce software, it has provided an example for using MySQL transactions in your applications.

在本指南中，您使用PHP PDO处理MySQL事务。尽管这不是有关设计电子商务软件的结论性文章，但它提供了在应用程序中使用MySQL事务的示例。

To learn more about the MySQL ACID model, consider visiting the InnoDB and the ACID Model guide from the official MySQL website. Visit our MySQL content page for more related tutorials, articles, and Q&A.

要了解有关MySQL ACID模型的更多信息，请考虑从MySQL官方网站访问InnoDB和ACID模型指南。请访问我们的MySQL内容页面，以获取更多相关的教程，文章和问答。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-use-the-pdo-php-extension-to-perform-mysql-transactions-in-php-on-ubuntu-18-04

mysql pdo.php

cukw6666

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql pdo.php_如何使用PDO PHP扩展在Ubuntu 18.04上PHP中执行MySQL事务

mysql pdo.phpThe author selected Open Sourcing Mental Illness to receive a donation as part of the Write for DOnations program. 作者选择了开放源性精神疾病作为Write for DOnations计划的一部分接受捐赠。介绍 (Introduction)A My...
复制链接

扫一扫