How to Use PostgreSQL PDO Driver with PHP Part 1

Written by Data Pilot
August 10, 2019
  • Composer
  • PDO driver
  • PostgreSQL
  • Redis
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will explain how to connect PHP to PostgreSQL with the PDO driver and create the PHP project structure with Composer, a dependency-management tool in PHP. Compose allows for verifying the libraries a project requires and it will install and update the libraries as needed. Be sure all the prerequisites are properly in place before beginning the exercises in this tutorial.

Prerequisites to Connect PHP to PostgreSQL with the PDO Driver.

  • PHP must be properly installed and configured. Check for the current PHP installation version by executing the php -v command. The result should look something like the following in the Linux system:
1
2
3
4
PHP 7.3.7-2+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Jul 25 2019 11:44:59) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.7, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.7-2+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

The result should resemble the following in a Windows system:

1
2
3
PHP 7.3.5 (cli) (built: May 1 2019 13:17:17) ( ZTS MSVC15 (Visual C++ 2017) x64 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.5, Copyright (c) 1998-2018 Zend Technologies
  • The PostgreSQL server must be properly installed, configured and running in the background.

Execute the following command to start the PostgreSQL server:

1
sudo service postgresql start

Now execute the following command to verify the service is running:

1
service postgresql status

The result should resemble the following:

1
2
3
4
5
6
7
8
9
— postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • Composer must be properly installed and configured. Entering the composer –version command should return the following results:
1
Composer 1.6.3 2018-01-31 16:28:17

PDO and PHP Data Objects

The PHP data objects, or PDO, defines a lightweight, unified interface for accessing the relational databases, or RDB, in PHP. Each database defines the driver specific to that database that implements the PDO interface. Each driver can also reveal the database-specific functions as a normal extension of the functions.

NOTE: Most PHP distributions includes PostgreSQL extension PDO_PGSQL by default. However, the extension can be edited in the php.ini file. Just remove the prefixed semicolon (;) to uncomment the following line with ;extension=php_pdo_pgsql.dll.

How to Create the PHP Project Structure with Composer

This section will explain how to use Composer to create the project-directory structure.

  1. Create a folder and name it something meaningful, in this case the folder will be named for the project directory as “postgreSQL_php”.

  2. Create a sub-folder and name it “control.” Then create a json file named composer.json and put the following json script inside the control file:

1
2
3
4
5
6
7
{
"autoload": {
"psr-4": {
"PostgreSQLPHP": "control/"
}
}
}

The above script handles the mapping of every class that will be created and added to the PostgreSQL namespace.

NOTE: The PHP Standard Recommendations isPSR-4: Improved Autoloading for using FQCN in a file path.

  1. Navigate to the project directory via the terminal then execute the below command. Note that in this case the system project directory is located at “var/www/html/postgreSQL_php”.
1
composer update

The above Composer command will download the declared libraries within the composer.json file and will generate an autoload file. Composer will also create a folder named vendor and insert all of the third-party libraries in it. However, in this case no third-party library was specified so composer will only generate the autoload file. The results should resemble the following.

1
2
3
4
Loading composer repositories with package information
Updating dependencies (including require-dev)
Nothing to install or update
Generating autoload files
  1. Create a PHP file named index.PHP in the root project directory using the following touch command:
1
touch index.php
  1. Create two additional files within the control folder named connection.php and config.ini and then repeat the same process used in the previous section in sequence.

First execute:

1
touch connection.php

Then execute:

1
touch config.ini

To verify the following file was created, use the ls command. The results should resemble the following:

1
2
bash/var/www/html/postgreSQL_php/control $ ls
config.ini connection.php

How to Create a PostgreSQL Database

Execute the following command in the terminal to create a database where the connection can be tested later:

1
sudo -u postgres psql createdb testDatabase

Execute the following command, in sequence, to verify the database was successfully created:

1
sudo -i -u postgres

then

1
l

The result should resemble the following:

1
2
3
4
5
6
7
8
9
10
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
template0 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testDatabase | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
(4 rows)

The above are the results for the database testDatabase.

How to Connect PHP to the PostgreSQL Database

With the project and the database properly set up, add functionalities to the files that were created in the previous section.

The Configuration for config.ini

Open the config.ini using the nano editor and add the following configuration by executing the following command:

1
sudo nano /var/www/html/postgreaSQL_php/control/config.ini

Now add the following PostgreSQL database parameters:

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

How to create the PHP code for the connection.php

Now create a PHP class named Connection inside the connection.php file with the same process used in the previous section. However, this time edit the connection.php file using nano with the following commands:

1
sudo nano /var/www/html/postgreSQL_php/control/connection.php

Now add the following PHP script in the 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
<?php
namespace PostgreSQLPHP;


/**
* Represents the Connection
*/
class Connection {

/**
* The Connection
* @var type
*/
private static $conn;

/**
* Connect to the database and return an instance of PDO object
* @return PDO
* @throws Exception
*/
public function connect() {

// The following script will parse the parameters in the config.ini configuration file
$params = parse_ini_file('config.ini');
if ($params === false) {
throw new Exception("Error reading database configuration file");
}
// connect to the postgresql database from the config.file parameters
$conStr = sprintf("pgsql:host=%s;dbname=%s;user=%s;password=%s",
$params['host'],
$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
*/
public static function get() {
if (null === static::$conn) {
static::$conn = new static();
}

return static::$conn;
}


}

The above code is a bit lengthy. Here is a breakdown:

  1. A singleton class Connection class was used. This will create only one instance and if an instance already exists while trying to create a new one, the class returns the reference to the old instance.

  2. Before connecting to a PostgreSQL database a new PDO instance class must be created.

The PHP code for the index.php

Below is the PHP script of the index.php file:

1
2
3
4
5
6
7
8
9
10
11
12
<?php
require 'vendor/autoload.php';

use PostgreSQLPHPConnection as Connection;

try {
Connection::get()->connect();
echo 'PostgreSQL Server : database connection has been established successfully.';
} catch (PDOException $e) {
echo $e->getMessage();
}
?>

The above code will catch a PDOException, if one should occur when connecting to the PostgreSQL database server.

With the connection setup, now update the autoload files using the following command:

1
composer dump-autoload -o

NOTE: Be sure to be in the project root directory when executing the above command.

The result should be: Generating optimized autoload files.

How to Test the PostgreSQL Connection with PHP

With the PHP scripts and configuration set up, test the new program using the built-in Webserver, however, this feature is only available in PHP versions 5.4 and above. Navigate to the project directory and run the following command:

1
PHP -S localhost:9090

The results should resemble the following:

1
2
3
Listening on http://localhost:9090
Document root is /var/www/html/postgreSQL_php
Press Ctrl-C to quit.

Navigate to the browser and type in localhost:9090.

The results should resemble the following:

1
PostgreSQL Server : database connection has been established successfully.

Conclusion

This tutorial covered how to connect PHP to PostgreSQL with the PDO driver. The article explained PDO and PHP data objects and how to create the PHP project structure with Composer. The tutorial also covered how to create a PostgreSQL database, how to connect PHP to the PostgreSQL database, how to create the PHP code, a breakdown of the code and how to test the PostgreSQL connection with PHP. Remember that testing the PostgreSQL connection using the built-in Webserver can only be done in PHP versions 5.4 and above.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

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

Get Started
Related Topics:
Additional Information:

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.