Delete Postgres data using transaction in PHP

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

SQL transaction is used to execute one or more statements. If the “transaction” is successful, then all of the changes in the transaction will be applied to the entire table. However, if an error occurs in any one statement in a transaction, any changes made as a result of that transaction will be rolled back, or canceled. PostgreSQL transaction provides atomicity, the “A” in the SQL ACID acronym that stands for Atomicity, Consistency, Isolation and Durability. ACID properties in a SQL server are used to ensure data integrity during a transaction. This tutorial will explain how to delete Postgres data using transaction in PHP via PHP PDO.

Prerequisites

  • A strong basic knowledge of PostgreSQL is required.
  • PostgreSQL must be properly installed and configured on the local system to be able to delete Postgres data using transaction in PHP via PHP PDO.
  • PHP must also be properly installed on the local system in a standalone version or as part of a package installer, such as XAMPP.
  • Composer must also be properly installed on the local system.

What is a PostgreSQL Transaction

PostgreSQL transaction provides atomicity to database operation against target data in one or multiple databases. All of the statements declared in a transaction must be successfully added to the database, or a total rollback will occur as if nothing was processed. This promotes data integrity and safety within the greater database system.

Following is the basic “transaction” syntax:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
 
try {
    $pdo->beginTransaction();
 
    $pdo->query("SELECT <field1>,<field2>,... FROM table");
 
    $stmt = $pdo->prepare("DELETE QUERY");
    $stmt->execute();
 
    $db->commit();
} catch (\PDOException $e) {
    $db->rollBack();
    throw $e;
}

Here is a breakdown of the above syntax:

  • The beginTransaction() method is used to initiate the transaction process.
  • The required query is then created.
  • The prepare() method is used to prepare the query.
  • The execute() method is then used to execute the query.
  • The commit() method is then called to permit the query to take effect against the target database, provided no errors are encountered during the process.

  • The rollback() method will be triggered should an error or exception be encountered.

Creating Sample Dataset

This section will cover creating a sample data set used for demo purpose in this tutorial.

  • Execute the following command to create the database:
1
create database restaurant
  • Now use the following command to create tables for the database:
1
2
3
4
5
CREATE TABLE IF NOT EXISTS food (
    id SERIAL PRIMARY KEY,
    menu_name CHARACTER VARYING(100),
    price INTEGER
);
  • Now insert the following sample data:
1
2
3
4
5
6
7
INSERT INTO food (id, menu_name, price)
VALUES
(1,'fried chicken', 100),
(2,'fried pork', 250),
(3,'beef stew', 290),
(4,'spaghetti', 100),
(5,'fried Turkey', 600);

The resulting table should resemble the following:

1
2
3
4
5
6
7
8
 id |   menu_name   | price
----+---------------+-------
  1 | fried chicken |   100
  2 | fried pork    |   250
  3 | beef stew     |   290
  4 | spaghetti     |   100
  5 | fried Turkey  |   600
(5 rows)

Project Structure

This section will explain how to create the project files. The following screenshot depicts how to structure the project directory:

-img src=”https://t.gyazo.com/teams/500rockets/40a2675ba3aa3a7b3f3b589e89590add.png” width=”632″ height=”456″-

How to delete data in PHP via transaction

This section will explain coding the PHP files that will process the delete operation via transaction in PHP.

Execute the following code to modify the PHP file named ‘phpdeletetrans.php’:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?php

namespace deletetransaction;

class phpdeletetrans {

    private $pdo;
 
    /**
     * Initialize the object with a specified PDO object
     * @param \PDO $pdo
     */

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

 /**
     * delete a food
     * @param int $id
     */

    public function deleteFood($id) {
        try {
            // we start the transaction
            $this->pdo->beginTransaction();
 
            // delete a Postgres data with via id
            // $sql = 'DELETE FROM food WHERE id = :id';
            $stmt = $this->pdo->prepare('DELETE FROM food WHERE id = :id');
            $stmt->bindValue(':id', $id);
     
           
 
            // commit the changes if no error encountered
            $this->pdo->commit();

            // this returns the number of rows deleted
            return $stmt->execute();

        } catch (\PDOException $e) {
            // we rollback the changes should we encounter an error
            $this->pdo->rollBack();
            throw $e;
        }
    }

   

}

Now add the following script to the ‘index.php’ file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
 
require 'vendor/autoload.php';
 
use deletetransaction\Connection as Connection;
use deletetransaction\phpdeletetrans as phpdeletetrans;
 
try {
    // this code will connect us to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $deleteTransaction = new phpdeletetrans($pdo);
    // we will delete a car with a specified id which is 1
    $deletedRows = $deleteTransaction->deleteFood(1);
    echo 'We deleted : ' . $deletedRows . ' row(s) ' . '<br>';
   
} catch (\PDOException $e) {
    echo $e->getMessage();
}

Now test the PHP application using the following URL: ‘http://localhost/phptransaction/index.php’

The output should resemble the following image:

The result can also be verified via the Postgres shell by calling the “food” table, as shown here:

1
2
3
4
5
6
7
8
9
restaurant=# SELECT * FROM food;
 id |        menu_name         | price
----+--------------------------+-------
  1 | Honey Glazed Onion Rings |     8
  3 | Fried Calamari           |     9
  4 | Antipasto Platter        |    10
  5 | Grilled Artichoke        |     7
  2 | Bison Burger             |    10
(5 ROWS)

Conclusion

This tutorial provided explanations and examples on how to delete Postgres data using transaction in PHP via PHP PDO. The tutorial first provided an overview of the PostgreSQL transaction function, the basic transaction form syntax and a breakdown of that syntax. The article then explained how to create a sample dataset, how to create tables for the database and how to insert the sample data into the table. The tutorial then covered the project structure, explaining how to create the project files, how to delete data in PHP via transaction and provided a URL to test the PHP application. Remember that the success of the function to delete Postgres data using transaction in PHP can also be verified via the Postgres shell.

The Code

Following is the code used for files not discussed in the previous sections.

Connection.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<?php
 
namespace updatetransaction;
 
/**
 * Represent the Connection
 */

class Connection {
 
    /**
     * Connection
     * @var type
     */

    private static $conn;
 
    /**
     * Connect to the database and return an instance of \PDO object
     * @return \PDO
     * @throws \Exception
     */

    public function connect() {
 
        // This code will read the parameters on the Db.ini file using the parse_ini_file() function
       
        $paramDb = parse_ini_file('param.ini');
        if ($paramDb === false) {
            throw new \Exception("Error reading database config file, please check param.ini file");
        }
        // The following code will connect us to the database using the parsed parameters
        $connString = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",
                $paramDb['host'],
                $paramDb['port'],
                $paramDb['database'],
                $paramDb['user'],
                $paramDb['password']);
 
        $pdo = new \PDO($connString);
        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
 
        return $pdo;
    }
 
    /**
     * this code will return an instance of the Connection object
     * @return type
     */

    public static function get() {
        if (null === static::$conn) {
            static::$conn = new static();
        }
 
        return static::$conn;
    }
 
    protected function __construct() {
       
    }
 
}

Param.ini

1
2
3
4
5
host=localhost  
port=5432
database=restaurant
user=postgres
password=1234

composer.json

1
2
3
4
5
6
7
{
    "autoload": {
        "psr-4": {
            "updatetransaction\": "app/"
        }
    }
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.