Retrieve Postgres Data using a Transaction in PHP

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

Introduction

If you’re a PHP developer, you may need to perform certain PostgreSQL operations from your PHP applications. It’s easy to interact with a PostgreSQL database using the PHP PDO. In this article, we’ll explain how to retrieve Postgres data using a transaction in PHP. We’ll show you how to organize your PHP project structure and walk you through the sample code you’ll need to get started.

Prerequisites

In order to follow along with the code examples in this tutorial, you’ll need to install and configure the following on your machine:

Creating Sample Dataset

In this section, we’ll create a sample dataset that we can use in our examples.

  • First, we’ll use the following command to create a database:
1
create database restaurant;
  • Then we’ll 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
);

Once our database and tables are created, we can insert sample data using the following INSERT statement:

1
2
3
4
5
6
7
INSERT INTO food (id, menu_name, price)
VALUES
(1,'Black Bean Veggie Burger', 9),
(2,'California Chicken Burger', 8),
(3,'Hickory Burger', 9),
(4,'Mushroom Swiss Burger', 8),
(5,'Grass-Fed Burger', 9);

If we perform a simple query to view the contents of the table, the result should look like this:

1
2
3
4
5
6
7
8
9
restaurant=# select * from food;
 id |         menu_name         | price
----+---------------------------+-------
  1 | Black Bean Veggie Burger  |     9
  2 | California Chicken Burger |     8
  3 | Hickory Burger            |     9
  4 | Mushroom Swiss Burger     |     8
  5 | Grass-Fed Burger          |     9
(5 rows)

Project Structure

The image below shows the project structure that we will be using for this tutorial.

How to retrieve Postgres data in PHP via transaction

In this section, we’ll show you the code that will retrieve Postgres data in PHP via transaction.

We’ll start by editing a PHP file called ‘phpretrievetrans.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
<?php

namespace retrievetransaction;

class phpretrievetrans {

    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 findFood() {
        try {
            // we start the transaction
            $this->pdo->beginTransaction();

            // we create our prepared statement providing the query.
            // The query will get all the data from Postgres table and sort the data by price.
            $stmt = $this->pdo->query('SELECT id, menu_name, price '
            . 'FROM food '
            . 'ORDER BY price');
            $food = [];
            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                $food[] = [
                    'id' => $row['id'],
                    'menu_name' => $row['menu_name'],
                    'price' => $row['price']
                ];
            }
     
           
 
            // commit the changes if no error encountered
            $this->pdo->commit();
            return $food;
        } catch (\PDOException $e) {
            // we rollback the changes should we encounter an error
            $this->pdo->rollBack();
            throw $e;
        }
    }

   

}

Feel free to copy and paste this sample code into your own file, but be sure to read through the comments in the script. The code contains many comments that make it clearer and easier to understand.

Shown below is the code that belongs in the ‘index.php’ file. This file acts as the entry point of our basic application:

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
<?php
 
require 'vendor/autoload.php';
 
use retrievetransaction\Connection as Connection;
use retrievetransaction\phpretrievetrans as phpretrievetrans;
 
try {
    // this code will connect us to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $retrieveTransaction = new phpretrievetrans($pdo);
   
    // get all stocks data
    $foods = $retrieveTransaction->findFood();
   
} catch (\PDOException $e) {
    echo $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Retrieve Postgres via Transaction in PHP</title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">
            <h1>Food Menu</h1>
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Menu Name</th>
                        <th>Price</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($foods as $food) : ?>
                        <tr>
                            <td><?php echo htmlspecialchars($food['id']) ?></td>
                            <td><?php echo htmlspecialchars($food['menu_name']); ?></td>
                            <td><?php echo htmlspecialchars($food['price']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </body>
</html>

We can use the following URL to test our application: http://localhost/retrievetransaction/index.php’

The output should look like this:

Conclusion

Retrieving data from PostgreSQL tables is just one of the many database operations you can perform from a PHP application. In this article, we showed you how to retrieve Postgres data using a transaction in PHP with the help of the PHP PDO. With our sample code to guide you, you’ll be able to perform Postgres operations from your own PHP applications.

The Code

The following code is used in other files that we did not discuss in this tutorial:

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
60
61
<?php
 
namespace retrievetransaction;
 
/**
 * 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": {
            "retrievetransaction\": "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.