PostgreSQL PHP: Transaction

http://www.postgresqltutorial.com/postgresql-php/transaction/




<?php
 
try {
    $pdo->beginTransaction();
 
    $pdo->query("SELECT * FROM table");
 
    $stmt = $pdo->prepare("UPDATE QUERY");
    $stmt->execute();
 
    $stmt = $pdo->prepare("ANOTHER UPADTE QUERY");
    $stmt->execute();
 
    $db->commit();
} catch (\PDOException $e) {
    $db->rollBack();
    throw $e;
}



CREATE TABLE accounts(
   id SERIAL PRIMARY KEY,
   first_name CHARACTER VARYING(100),
   last_name CHARACTER VARYING(100)
);
 
CREATE TABLE plans(
   id SERIAL PRIMARY KEY,
   plan CHARACTER VARYING(10) NOT NULL
);
 
CREATE TABLE account_plans(
   account_id INTEGER NOT NULL,
   plan_id INTEGER NOT NULL,
   effective_date DATE NOT NULL,
   PRIMARY KEY (account_id,plan_id),
   FOREIGN KEY(account_id) REFERENCES accounts(id),
   FOREIGN KEY(plan_id) REFERENCES plans(id)
 
);



INSERT INTO plans(plan) VALUES('SILVER'),('GOLD'),('PLATINUM');




The following addAccount() method performs two main steps:

    First, insert an account into the accounts table and returns the account id.
    Then, assign the account a specific plan by inserting a new row into the account_plans table.

At the beginning of the method, we call the beginTransaction() method of the PDO object to start the transaction.

If all the steps succeed, we call the commit() method to save the changes. In case an exception occurs in any step, we roll back the changes by calling the rollback() method in the catch block.



/**
     * Add a new account
     * @param string $firstName
     * @param string $lastName
     * @param int $planId
     * @param date $effectiveDate
     */
    public function addAccount($firstName, $lastName, $planId, $effectiveDate) {
        try {
            // start the transaction
            $this->pdo->beginTransaction();
 
            // insert an account and get the ID back
            $accountId = $this->insertAccount($firstName, $lastName);
 
            // add plan for the account
            $this->insertPlan($accountId, $planId, $effectiveDate);
 
            // commit the changes
            $this->pdo->commit();
        } catch (\PDOException $e) {
            // rollback the changes
            $this->pdo->rollBack();
            throw $e;
        }
    }
    
    
    
    
    
The addAccount() method uses two other private methods: insertAccount() and insertPlan() as the following:


	/**
     * 
     * @param string $firstName
     * @param string $lastName
     * @return int
     */
    private function insertAccount($firstName, $lastName) {
        $stmt = $this->pdo->prepare(
                'INSERT INTO accounts(first_name,last_name) '
                . 'VALUES(:first_name,:last_name)');
 
        $stmt->execute([
            ':first_name' => $firstName,
            ':last_name' => $lastName
        ]);
 
        return $this->pdo->lastInsertId('accounts_id_seq');
    }



   /**
     * insert a new plan for an account
     * @param int $accountId
     * @param int $planId
     * @param int $effectiveDate
     * @return bool
     */
    private function insertPlan($accountId, $planId, $effectiveDate) {
        $stmt = $this->pdo->prepare(
                'INSERT INTO account_plans(account_id,plan_id,effective_date) '
                . 'VALUES(:account_id,:plan_id,:effective_date)');
 
        return $stmt->execute([
                    ':account_id' => $accountId,
                    ':plan_id' => $planId,
                    ':effective_date' => $effectiveDate,
        ]);
    }
    
    


To test the AccountDB class, you use the following code in the index.php file.


<?php
 
require 'vendor/autoload.php';
 
use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\AccountDB as AccountDB;
 
try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
 
    $accountDB = new AccountDB($pdo);
 
    // add accounts
    $accountDB->addAccount('John', 'Doe', 1, date('Y-m-d'));
    $accountDB->addAccount('Linda', 'Williams', 2, date('Y-m-d'));
    $accountDB->addAccount('Maria', 'Miller', 3, date('Y-m-d'));
 
 
    echo 'The new accounts have been added.' . '<br>';
    // 
    $accountDB->addAccount('Susan', 'Wilson', 99, date('Y-m-d'));
} catch (\PDOException $e) {
    echo $e->getMessage();
}



How it works.

    First, connect to the PostgresSQL database.
    Second, insert three accounts with silver, gold, and platinum levels.
    Third, try to insert one more account but with a plan id that does not exist in the plans table. Based on the input, the step of assigning the plan to the account fails that cause the whole transaction to be rolled back.


stocks=# SELECT * FROM accounts;
 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Linda      | Williams
  3 | Maria      | Miller
(3 rows)
 
stocks=# SELECT * FROM account_plans;
 account_id | plan_id | effective_date
------------+---------+----------------
          1 |       1 | 2016-06-13
          2 |       2 | 2016-06-13
          3 |       3 | 2016-06-13
(3 rows)