How to perform MySQL transactions using PHP?

Executing MySQL transactions in PHP ensures data consistency and integrity. The steps include: Establishing a database connection Opening a transaction Executing a query Submitting the transaction (all queries succeed) Rolling back the transaction (any query fails) Practical case: In the shopping cart application, if any query fails, the transaction will be rolled back and the shopping cart and products will be undone. Changes to the table; if all queries succeed, the transaction is committed, saving the changes permanently.

#How to perform MySQL transactions using PHP?

A transaction is a series of operations in the database, either all of them are submitted successfully or all of them fail to be rolled back. Using transactions in PHP ensures data consistency and integrity.

Steps:

1. Establish a database connection: Use the mysqli_connect() function to establish a connection with the MySQL database .
2. Start a transaction: Use the mysqli_begin_transaction() function to start a transaction.
3. Execute query: Execute all necessary queries. For example, updating a table or inserting data.
4. Commit transaction: If all queries are successful, use the mysqli_commit() function to commit the transaction. This will save the changes permanently.
5. Rollback transaction: If any query fails, use the mysqli_rollback() function to roll back the transaction. This will undo all changes made within the transaction.

Practical Example:

Consider a simple shopping cart application where users add items to their shopping cart. The following code shows how to use transactions to achieve this functionality:

// 建立数据库连接
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 开启事务
$conn->begin_transaction();

// 插入新项目到购物车表中
$sql = "INSERT INTO cart (product_id, user_id, quantity) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("iii", $productId, $userId, $quantity);
$stmt->execute();

// 更新产品表中的库存
$sql = "UPDATE products SET stock = stock - ? WHERE product_id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $quantity, $productId);
$stmt->execute();

// 检查是否有任何查询失败
if ($stmt->error) {
    // 回滚事务
    $conn->rollback();
    echo "添加商品到购物车失败！";
    exit();
}

// 提交事务
$conn->commit();
echo "商品已成功添加到购物车！";

In the above example, if any query (inserting into the shopping cart table or updating the products table) fails, the transaction will be rolled back and the changes in the shopping cart will be will be undone. If all queries succeed, the transaction is committed and the changes to the shopping cart and product tables are persisted.

The above is the detailed content of How to perform MySQL transactions using PHP?. For more information, please follow other related articles on the PHP Chinese website!