How to fetch Postgres in PHP

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

Introduction

The tutorial will explain how to fetch Postgres in PHP via the PHP Data Objects API. The PHP PDO is a lightweight interface used to access databases in PHP and establishes a uniform application programming interface, or API, to interact with various database systems. One of its many functions is to allow for the quick and easy retrieval of information across PHP platforms. This tutorial will explain how to fetch Postgres in a PHP database via the PHP application.

Prerequisites

  • A text editor. While various editors can be used, Visual Studio Code was is used in this tutorial and can be downloaded from the Visual Studio Code website.

  • PostgreSQL must be properly installed and configured on the local system.

  • Internet access for downloading any necessary files.

  • XAMPP must be properly installed and configured on the local system.

  • Composer must be properly installed on the local system, allowing for better management of project dependencies. Refer to the following image:

Fetch PostgreSQL rows in PHP

This section will cover how to fetch data from the Postgres database via the PHP application.

Project Structure

The project structure should resemble the following:

Following is a detailed breakdown of the above file:

  • Connection.php: This file is responsible for the PHP-PostgreSQL database connection.
  • db_param.ini: This file stores the database connection parameters.
  • phpfetch.php: This PHP file holds the script for fetching records from the PostgresSQL database.
  • vendor: This directory stores all of project’s the third-party dependencies.
  • composer.json: This file contains all of the dependencies that are required for the project.
  • index.php: This PHP file is the entry point of the application, via a browser.

Create Sample dataset

This section will explain how to create the sample dataset used for this tutorial.

  • First, create a database named “Personnel” using the create database personnel command.
  • Now execute the following command to create a table named “Person”:
1
2
3
4
5
6
CREATE TABLE person(
    id INT PRIMARY KEY NOT NULL,
    p_name VARCHAR NOT NULL,
    p_lastname VARCHAR NOT NULL,
    p_age INT
);
  • Now insert the following sample data:
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO person (id, p_name, p_lastname, p_age)
VALUES
   (1,'angie','locsin', 23),
   (2,'tony','lacson', 24),
   (3,'duke','samuel', 24),
   (4,'gina','gener', 25),
   (5,'abi','galisanao', 21),
   (6,'john','dune', 23),
   (7,'yeshua','galisanao', 28),
   (8,'nora','burn', 23),
   (9,'raizel','galisanao', 35);

The ‘person’ table should now resemble this:

1
2
3
4
5
6
7
8
9
10
11
12
 id | p_name | p_lastname | p_age
----+--------+------------+-------
  1 | angie  | locsin     |    23
  2 | tony   | lacson     |    24
  3 | duke   | samuel     |    24
  4 | gina   | gener      |    25
  5 | abi    | galisanao  |    21
  6 | john   | dune       |    23
  7 | yeshua | galisanao  |    28
  8 | nora   | burn       |    23
  9 | raizel | galisanao  |    35
(9 rows)

Fetch All data in PostgreSQL via PHP

This section will explain how to fetch all rows in the target table via PHP.

Edit the phpfetch.php file and append the following PHP script:

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
<?php

namespace phpfetchpostgres;

class phpfetch{

       /**
     * PDO object
     * @var \PDO
     */

    private $pdo;
 
    /**
     * We initialize the object with a defined PDO object
     * @param \PDO $pdo
     */

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

     /**
     * Return all rows in the person table
     * @return array
     */

        public function all() {
        $stmt = $this->pdo->query('SELECT id, p_name, p_lastname, p_age '
                . 'FROM person '
                . 'ORDER BY p_age');
        $person = [];
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            $person[] = [
                'id' => $row['id'],
                'p_name' => $row['p_name'],
                'p_lastname' => $row['p_lastname'],
                'p_age' => $row['p_age']
            ];
        }
        return $person;
    }

}

Now execute the following script to implement everything that has thus far been configured inside the index.php file:

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
<?php
require 'vendor/autoload.php';
 
use phpfetchpostgres\Connection as Connection;
use phpfetchpostgres\phpfetch as PersonDB;
 
try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $personDB = new PersonDB($pdo);
    // get all stocks data
    $persons = $personDB->all();
} catch (\PDOException $e) {
    echo $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Fetch PostgreSQL data via PHP DEMO</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>List of Person</h1>
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>Last Name</th>
                        <th>Age</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($persons as $person) : ?>
                        <tr>
                            <td><?php echo htmlspecialchars($person['id']) ?></td>
                            <td><?php echo htmlspecialchars($person['p_name']); ?></td>
                            <td><?php echo htmlspecialchars($person['p_lastname']); ?></td>
                            <td><?php echo htmlspecialchars($person['p_age']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </body>
</html>

Now test the application via the following URL: ‘http://localhost/phpfetchpostgres/index.php’

The results should resemble the following:

Conclusion

This tutorial covered how to fetch Postgres in a PHP database via the PHP application. The tutorial provided an example of the PHP project structure file and then gave a detailed breakdown of that file. The article then explained how to create the sample dataset used for the examples in this tutorial, showed how to fetch all of the data in PostgreSQL via PHP and then how to test the application via the provided URL. While various text editors can be used, the download link was provided to Visual Studio Code used to execute the examples in this tutorial.

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.