PHP SQL Server: Transaction

Summary: In this tutorial, you will learn how to perform a transaction on an SQL Server using PHP PDO.

This tutorial begins where the deleting data in a table from PHP tutorial left off.

Creating sample data

Step 1. Open SQL Server Management Studio (SSMS) and connect to the BookStore database on your SQL Server.

Step 2. Execute the following statements to insert books into the Books table, assign these books to the author with id 2, and update inventories for these books in the Inventories table:

INSERT INTO Books (Title, Publisher, ISBN, PublishedDate) VALUES
('Mastering SQL: A Comprehensive Guide', 'Tech Books Publishing', '978-1234567890', '2022-01-15'),
('The Art of Database Design', 'Expert Press', '978-0987654321', '2021-06-10'),
('SQL Queries for Mere Mortals', 'Practical SQL Publishing', '978-1122334455', '2023-03-21'),
('Advanced SQL Programming Techniques', 'Pro Code Press', '978-6677889900', '2020-09-30'),
('Database Systems: Theory and Practice', 'Academic Press', '978-5566778899', '2022-11-05');

DECLARE @BookID1 INT, @BookID2 INT, @BookID3 INT, @BookID4 INT, @BookID5 INT;

SELECT @BookID1 = BookID FROM Books WHERE ISBN = '978-1234567890';
SELECT @BookID2 = BookID FROM Books WHERE ISBN = '978-0987654321';
SELECT @BookID3 = BookID FROM Books WHERE ISBN = '978-1122334455';
SELECT @BookID4 = BookID FROM Books WHERE ISBN = '978-6677889900';
SELECT @BookID5 = BookID FROM Books WHERE ISBN = '978-5566778899';

-- Insert records into the BookAuthors table
INSERT INTO BookAuthors (BookID, AuthorID)
VALUES (@BookID1, 2),
       (@BookID2, 2),
       (@BookID3, 2),
       (@BookID4, 2),
       (@BookID5, 2);

INSERT INTO Inventories (BookID, Qty)
SELECT BookID, ABS(CHECKSUM(NEWID()) % 101) + 100
FROM Books;


-- Insert customers
INSERT INTO [dbo].[Customers] (FirstName, LastName, Email, PhoneNumber, Address) VALUES
('John', 'Doe', '[email protected]', '123-456-7890', '123 Elm Street, Springfield, IL 62701'),
('Jane', 'Smith', '[email protected]', '234-567-8901', '456 Oak Avenue, Metropolis, NY 10001');Code language: PHP (php)

Performing a transaction

Create a new file orderdb.php and define the OrderDB class that handles the transaction:

<?php

class OrderDB
{
    public function __construct(private \PDO $conn)
    {
        $this->conn = $conn;
    }

    public function create($customerId, $bookId, $quantity, $price, $orderDate)
    {
        try {
            // Begin a transaction
            $this->conn->beginTransaction();

            // Check inventory
            $this->checkInventory($bookId, $quantity);

            // Insert order and get the new order ID
            $orderId = $this->insertOrder($customerId, $orderDate, $price * $quantity);

            // Insert order details
            $this->insertOrderDetails($orderId, $bookId, $quantity, $price);

            // Update inventory
            $this->updateInventory($bookId, $quantity);

            // Commit the transaction
            $this->conn->commit();

            return $orderId;

        } catch (Exception $e) {
            // Rollback the transaction on error
            $this->conn->rollBack();
            throw new Exception("Failed to create order: " . $e->getMessage());
        }
    }

    private function checkInventory($bookId, $quantity)
    {
        $sql = 'SELECT Qty FROM Inventories WHERE BookId = :bookId';
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['bookId' => $bookId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row === false || $row['Qty'] < $quantity) {
            throw new Exception("Insufficient inventory");
        }
    }

    private function insertOrder($customerId, $orderDate, $totalAmount)
    {
        $sql = 'INSERT INTO Orders (OrderDate, CustomerId, TotalAmount) 
                VALUES (:orderDate, :customerId, :totalAmount)';

        $stmt = $this->conn->prepare($sql);
        $stmt->execute([
            'orderDate' => $orderDate,
            'customerId' => $customerId,
            'totalAmount' => $totalAmount
        ]);

        return $this->conn->lastInsertId();
    }

    private function insertOrderDetails($orderId, $bookId, $quantity, $price)
    {
        $sql = 'INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price) 
                VALUES (:orderId, :bookId, :quantity, :price)';

        $stmt = $this->conn->prepare($sql);
        
        $stmt->execute([
            'orderId' => $orderId,
            'bookId' => $bookId,
            'quantity' => $quantity,
            'price' => $price
        ]);
    }

    private function updateInventory($bookId, $quantity)
    {
        $sql = "UPDATE Inventories 
                SET Qty = Qty - :quantity 
                WHERE BookId = :bookId";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute([
            'quantity' => $quantity,
            'bookId' => $bookId
        ]);
    }
}Code language: PHP (php)

Running the app

Step 1. Modify the index.php file to create an order using the create() method of an OrderDB object:

<?php

require_once 'orderdb.php';

// Connect to the SQL Server
$conn = require_once 'connect.php';
$orderDB = new OrderDB($conn);

// Create a new order
$customerId = 1;
$bookId = 1;
$quantity = 5;
$price = 19.99;
$orderDate = '2024-07-31';

$orderId = $orderDB->create(
    $customerId,
    $bookId,
    $quantity,
    $price,
    '2024-07-27'
);

echo "New Order ID: " . $orderId;Code language: PHP (php)

Step 2. Run the index.php on the web browser. It’ll return the following output:

New Order ID: 1Code language: PHP (php)

This means that the application has successfully created an order with ID 1.

Summary

• Call the beginTransaction() method of the PDO object to start a transaction.
• Call the commit() method of the PDO object to apply the changes permanently to the database.
• Call the rollback() method of the PDO object to roll back the transaction.