pdo transaction

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