MySQL Transactions in PHP (Commit and Rollback)

A transaction means: “either everything succeeds, or nothing changes.” This is critical in real apps: - placing an order (order + order_items + payment log) - transferring money (debit + credit) - updating inventory + creating invoice ## The problem without transactions If you insert the order, then fail inserting order_items, your database becomes messy. ## Transaction flow ```mermaid flowchart TD A[Begin Transaction] --> B[Query 1] B --> C[Query 2] C --> D{All OK?} D -->|Yes| E[Commit] D -->|No| F[Rollback] ``` ## Example: Create order + items ```php <?php require_once "config.php"; try { $pdo->beginTransaction(); $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (:user_id, :total)"); $stmt->execute(["user_id" => $userId, "total" => $total]); $orderId = (int)$pdo->lastInsertId(); $itemStmt = $pdo->prepare( "INSERT INTO order_items (order_id, product_id, qty, price) VALUES (:order_id, :product_id, :qty, :price)" ); foreach ($items as $it) { $itemStmt->execute([ "order_id" => $orderId, "product_id" => $it["product_id"], "qty" => $it["qty"], "price" => $it["price"], ]); } $pdo->commit(); echo "Order created successfully"; } catch (Throwable $e) { $pdo->rollBack(); echo "Order failed safely (no partial data saved)"; } ?> ``` ## Pro tip Use transactions whenever multiple queries must stay consistent. > Next: Database indexing, why some queries become slow and how to fix them.