1<?php
2/* Begin a transaction, turning off autocommit */
3$dbh->beginTransaction();
4
5try {
6 /* Change the database schema and data */
7 $sth = $dbh->exec("DROP TABLE fruit");
8 $sth = $dbh->exec("UPDATE dessert
9 SET name = 'hamburger'");
10}
11catch(Exception $e){
12 /* Recognize mistake and roll back changes */
13 $dbh->rollBack();
14}
15/* Commit changes */
16$dbh->commit();
1$pdo = new PDO(
2 $dsn,
3 $username,
4 $password,
5 array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
6);
7
8try {
9 $statement = $pdo->prepare("UPDATE user SET name = :name");
10
11 $pdo->beginTransaction();
12
13 $statement->execute(["name"=>'Bob']);
14 $statement->execute(["name"=>'Joe']);
15
16 $pdo->commit();
17}
18catch (\Exception $e) {
19 if ($pdo->inTransaction()) {
20 $pdo->rollback();
21 // If we got here our two data updates are not in the database
22 }
23 throw $e;
24}
25
1// In this example we are using MySQL but this applies to any database that has support for transactions
2$db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname . ';charset=utf8', $username, $password);
3
4// Make sure that PDO will throw an exception in case of error to make error handling easier
5$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
6
7try {
8 // From this point and until the transaction is being committed every change to the database can be reverted
9 $db->beginTransaction();
10
11 // Insert the metadata of the order into the database
12 $preparedStatement = $db->prepare(
13 'INSERT INTO `orders` (`order_id`, `name`, `address`, `created_at`)
14 VALUES (:name, :address, :telephone, :created_at)'
15 );
16
17 $preparedStatement->execute([
18 'name' => $name,
19 'address' => $address,
20 'telephone' => $telephone,
21 'created_at' => time(),
22 ]);
23
24 // Get the generated `order_id`
25 $orderId = $db->lastInsertId();
26
27 // Construct the query for inserting the products of the order
28 $insertProductsQuery = 'INSERT INTO `orders_products` (`order_id`, `product_id`, `quantity`) VALUES';
29
30 $count = 0;
31 foreach ( $products as $productId => $quantity ) {
32 $insertProductsQuery .= ' (:order_id' . $count . ', :product_id' . $count . ', :quantity' . $count . ')';
33
34 $insertProductsParams['order_id' . $count] = $orderId;
35 $insertProductsParams['product_id' . $count] = $productId;
36 $insertProductsParams['quantity' . $count] = $quantity;
37
38 ++$count;
39 }
40
41 // Insert the products included in the order into the database
42 $preparedStatement = $db->prepare($insertProductsQuery);
43 $preparedStatement->execute($insertProductsParams);
44
45 // Make the changes to the database permanent
46 $db->commit();
47}
48catch ( PDOException $e ) {
49 // Failed to insert the order into the database so we rollback any changes
50 $db->rollback();
51 throw $e;
52}
53