{"id":4427,"date":"2024-07-31T09:38:22","date_gmt":"2024-07-31T02:38:22","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4427"},"modified":"2024-08-18T10:15:33","modified_gmt":"2024-08-18T03:15:33","slug":"php-sql-server-transaction","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-transaction\/","title":{"rendered":"PHP SQL Server: Transaction"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to perform a transaction on an SQL Server using PHP PDO.<\/p>\n\n\n\n

This tutorial begins where\u00a0the deleting data in a table from PHP tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Creating sample data #<\/a><\/h2>\n\n\n\n

Step 1. Open SQL Server Management Studio (SSMS) and connect to the BookStore<\/code> database on your SQL Server.<\/p>\n\n\n\n

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:<\/p>\n\n\n

INSERT INTO Books (Title, Publisher, ISBN, PublishedDate) VALUES\n('Mastering SQL: A Comprehensive Guide'<\/span>, 'Tech Books Publishing'<\/span>, '978-1234567890'<\/span>, '2022-01-15'<\/span>),\n('The Art of Database Design'<\/span>, 'Expert Press'<\/span>, '978-0987654321'<\/span>, '2021-06-10'<\/span>),\n('SQL Queries for Mere Mortals'<\/span>, 'Practical SQL Publishing'<\/span>, '978-1122334455'<\/span>, '2023-03-21'<\/span>),\n('Advanced SQL Programming Techniques'<\/span>, 'Pro Code Press'<\/span>, '978-6677889900'<\/span>, '2020-09-30'<\/span>),\n('Database Systems: Theory and Practice'<\/span>, 'Academic Press'<\/span>, '978-5566778899'<\/span>, '2022-11-05'<\/span>);\n\nDECLARE<\/span> @BookID1 INT, @BookID2 INT, @BookID3 INT, @BookID4 INT, @BookID5 INT;\n\nSELECT @BookID1 = BookID FROM Books WHERE ISBN = '978-1234567890'<\/span>;\nSELECT @BookID2 = BookID FROM Books WHERE ISBN = '978-0987654321'<\/span>;\nSELECT @BookID3 = BookID FROM Books WHERE ISBN = '978-1122334455'<\/span>;\nSELECT @BookID4 = BookID FROM Books WHERE ISBN = '978-6677889900'<\/span>;\nSELECT @BookID5 = BookID FROM Books WHERE ISBN = '978-5566778899'<\/span>;\n\n-- Insert records into the BookAuthors table\nINSERT INTO BookAuthors (BookID, AuthorID)\nVALUES (@BookID1, 2<\/span>),\n       (@BookID2, 2<\/span>),\n       (@BookID3, 2<\/span>),\n       (@BookID4, 2<\/span>),\n       (@BookID5, 2<\/span>);\n\nINSERT INTO Inventories (BookID, Qty)\nSELECT BookID, ABS(CHECKSUM(NEWID()) % 101<\/span>) + 100<\/span>\nFROM Books;\n\n\n-- Insert customers\nINSERT INTO [dbo].[Customers] (FirstName, LastName, Email, PhoneNumber, Address) VALUES\n('John'<\/span>, 'Doe'<\/span>, 'john.doe@gmail.com'<\/span>, '123-456-7890'<\/span>, '123 Elm Street, Springfield, IL 62701'<\/span>),\n('Jane'<\/span>, 'Smith'<\/span>, 'jane.smith@yahoo.com'<\/span>, '234-567-8901'<\/span>, '456 Oak Avenue, Metropolis, NY 10001'<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Performing a transaction #<\/a><\/h2>\n\n\n\n
Create a new file orderdb.php<\/code> and define the OrderDB<\/code> class that handles the transaction:<\/p>\n\n\n
<?php<\/span>\n\nclass<\/span> OrderDB<\/span>\n<\/span>{\n    public<\/span> function<\/span> __construct<\/span>(private \\PDO $conn)<\/span>\n    <\/span>{\n        $this<\/span>->conn = $conn;\n    }\n\n    public<\/span> function<\/span> create<\/span>($customerId, $bookId, $quantity, $price, $orderDate)<\/span>\n    <\/span>{\n        try<\/span> {\n            \/\/ Begin a transaction<\/span>\n            $this<\/span>->conn->beginTransaction();\n\n            \/\/ Check inventory<\/span>\n            $this<\/span>->checkInventory($bookId, $quantity);\n\n            \/\/ Insert order and get the new order ID<\/span>\n            $orderId = $this<\/span>->insertOrder($customerId, $orderDate, $price * $quantity);\n\n            \/\/ Insert order details<\/span>\n            $this<\/span>->insertOrderDetails($orderId, $bookId, $quantity, $price);\n\n            \/\/ Update inventory<\/span>\n            $this<\/span>->updateInventory($bookId, $quantity);\n\n            \/\/ Commit the transaction<\/span>\n            $this<\/span>->conn->commit();\n\n            return<\/span> $orderId;\n\n        } catch<\/span> (Exception<\/span> $e) {\n            \/\/ Rollback the transaction on error<\/span>\n            $this<\/span>->conn->rollBack();\n            throw<\/span> new<\/span> Exception<\/span>(\"Failed to create order: \"<\/span> . $e->getMessage());\n        }\n    }\n\n    private<\/span> function<\/span> checkInventory<\/span>($bookId, $quantity)<\/span>\n    <\/span>{\n        $sql = 'SELECT Qty FROM Inventories WHERE BookId = :bookId'<\/span>;\n        $stmt = $this<\/span>->conn->prepare($sql);\n        $stmt->execute(['bookId'<\/span> => $bookId]);\n        $row = $stmt->fetch(PDO::FETCH_ASSOC);\n\n        if<\/span> ($row === false<\/span> || $row['Qty'<\/span>] < $quantity) {\n            throw<\/span> new<\/span> Exception<\/span>(\"Insufficient inventory\"<\/span>);\n        }\n    }\n\n    private<\/span> function<\/span> insertOrder<\/span>($customerId, $orderDate, $totalAmount)<\/span>\n    <\/span>{\n        $sql = 'INSERT INTO Orders (OrderDate, CustomerId, TotalAmount) \n                VALUES (:orderDate, :customerId, :totalAmount)'<\/span>;\n\n        $stmt = $this<\/span>->conn->prepare($sql);\n        $stmt->execute([\n            'orderDate'<\/span> => $orderDate,\n            'customerId'<\/span> => $customerId,\n            'totalAmount'<\/span> => $totalAmount\n        ]);\n\n        return<\/span> $this<\/span>->conn->lastInsertId();\n    }\n\n    private<\/span> function<\/span> insertOrderDetails<\/span>($orderId, $bookId, $quantity, $price)<\/span>\n    <\/span>{\n        $sql = 'INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price) \n                VALUES (:orderId, :bookId, :quantity, :price)'<\/span>;\n\n        $stmt = $this<\/span>->conn->prepare($sql);\n        \n        $stmt->execute([\n            'orderId'<\/span> => $orderId,\n            'bookId'<\/span> => $bookId,\n            'quantity'<\/span> => $quantity,\n            'price'<\/span> => $price\n        ]);\n    }\n\n    private<\/span> function<\/span> updateInventory<\/span>($bookId, $quantity)<\/span>\n    <\/span>{\n        $sql = \"UPDATE Inventories \n                SET Qty = Qty - :quantity \n                WHERE BookId = :bookId\"<\/span>;\n\n        $stmt = $this<\/span>->conn->prepare($sql);\n        $stmt->execute([\n            'quantity'<\/span> => $quantity,\n            'bookId'<\/span> => $bookId\n        ]);\n    }\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Running the app #<\/a><\/h2>\n\n\n\n
Step 1. Modify the index.php<\/code> file to create an order using the create()<\/code> method of an OrderDB<\/code> object:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'orderdb.php'<\/span>;\n\n\/\/ Connect to the SQL Server<\/span>\n$conn = require_once<\/span> 'connect.php'<\/span>;\n$orderDB = new<\/span> OrderDB($conn);\n\n\/\/ Create a new order<\/span>\n$customerId = 1<\/span>;\n$bookId = 1<\/span>;\n$quantity = 5<\/span>;\n$price = 19.99<\/span>;\n$orderDate = '2024-07-31'<\/span>;\n\n$orderId = $orderDB->create(\n    $customerId,\n    $bookId,\n    $quantity,\n    $price,\n    '2024-07-27'<\/span>\n);\n\necho<\/span> \"New Order ID: \"<\/span> . $orderId;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Run the index.php<\/code> on the web browser. It’ll return the following output:<\/p>\n\n\n
New<\/span> Order ID: 1<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
This means that the application has successfully created an order with ID<\/code> 1.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Call the beginTransaction()<\/code> method of the PDO object to start a transaction.<\/li>\n\n\n\n
  • Call the commit()<\/code> method of the PDO object to apply the changes permanently to the database.<\/li>\n\n\n\n
  • Call the rollback()<\/code> method of the PDO object to roll back the transaction.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t