Enable PDO_PGSQL driver

Most PHP distributions include the PostgreSQL extensionPDO_PGSQL by default so you don’t need to do any further configuration in PHP.

However, if this is not the case, you can enable the extension by editing thephp.ini file to uncomment the following line:

;extension=php_pdo_pgsql.dll

To uncomment the line, you remove the semicolon (;) at the beginning of the line and restart the web server.

extension=php_pdo_pgsql.dll

Create a PHP project structure with Composer

The Composer is a tool for managing dependency that allows you to declare the PHP library in a project and manage the update automatically.

We will use the Composer to set up the project structure of all the projects that we will be working on.

First, create the  postgresqlphpconnect folder in the webroot folder to store the project files.

Next, create the app folder and a newcomposer.json file in the  postgresqlphpconnect folder with the following content:

{    "autoload": {        "psr-4": {            "PostgreSQLTutorial\\":"app/"        }    }}

It means that every class that you create in theapp folder will map to thePostgreSQLTutorial namespace.

Then, go to the window terminal, navigate to the  postgresqlphpconnect folder, and type the following command:

composerupdate

This command instructs the Composer to download the declared libraries in thecomposer.json file and generate an autoload file.

The command will also place all third-party libraries in the newly createdvendor folder. Because we don’t declare any library in thecomposer.json file, it generates the autoload file.

Loading composer repositories with package informationUpdating dependencies (includingrequire-dev)Nothing to install or updateGenerating autoload files

After that, create theindex.php file in the  postgresqlphpconnect folder.

Finally, create two more files in theapp folder:Connection.php anddatabase.ini.

The project structure will look like the following picture:

Connect to the PostgreSQL database

First,create a new database namedstocks for the demonstration.

CREATE DATABASE stocks;

Next, use thedatabase.ini file to store the PostgreSQL database parameters as follows:

host=localhostport=5432database=stocksuser=postgrespassword=postgres

Then, create a new class calledConnection in theConnection.php file.

<?phpnamespace PostgreSQLTutorial;/** * Represent the Connection */classConnection {    /**     * Connection     * @var type     */    private static $conn;    /**     * Connect to the database and return an instance of \PDO object     * @return \PDO     * @throws \Exception     */    publicfunction connect() {        // read parametersin the iniconfiguration file        $params= parse_ini_file('database.ini');        if ($params=== false) {            throw new \Exception("Error reading database configuration file");        }        // connect to the postgresqldatabase        $conStr= sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s",                $params['host'],                $params['port'],                $params['database'],                $params['user'],                $params['password']);        $pdo= new \PDO($conStr);        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);        return $pdo;    }    /**     * return an instance of the Connection object     * @return type     */    publicstatic function get() {        if (null === static::$conn) {            static::$conn= newstatic();        }        return static::$conn;    }    protectedfunction __construct() {    }    private function __clone() {    }    private function __wakeup() {    }}

How it works.

• TheConnection class is a singleton class. It means that you can create only one instance for the class. If the instance already exists and you try to create a new one, the class will return the existing reference.
• To connect to a PostgreSQL database, you need to create a new instance of the PDO class. In theconnect() method, we read the database configuration parameters in thedatabase.ini file, construct a connection string, and pass it to thePDO constructor.

After that, place the following code in theindex.php file.

<?phprequire 'vendor/autoload.php';use PostgreSQLTutorial\Connection as Connection;try {    Connection::get()->connect();    echo 'A connection to the PostgreSQL database sever has been established successfully.';}catch (\PDOException $e) {    echo $e->getMessage();}

PHP throws a\PDOException if there is an exception occurs when connecting to the PostgreSQL database server, therefore, you need to place the code to create a newPDO object inside the  try...catch block to handle the exception.

Run the following composer command to update the autoload files:

composerdump-autoload -o

Output:

Generating optimized autoload files

Finally, launch theindex.php file from the web browser to test it.

A connection to the PostgreSQL database sever has been established successfully.

If you want to see the exception that may occur, you can change the parameters in thedatabase.ini file to an invalid one and test it.

The following is the error message when the password is invalid.

SQLSTATE[08006] [7] FATAL: password authentication failedfor user"postgres"

And the following is the error message when the database is invalid.

SQLSTATE[08006] [7] FATAL: database"stockss" does not exist

In this tutorial, you have learned how to connect to the PostgreSQL database from a PHP application using the PDO API. We will reuse theConnection class in the subsequent tutorials.

Last updated onJanuary 30, 2024

On this page

• Enable PDO_PGSQL driver
• Create a PHP project structure with Composer
• Connect to the PostgreSQL database