Summary: in this tutorial, you will learn how to setup a simple project structure and connect to the PostgreSQL database using PHP PDO API.
Enable PDO_PGSQL driver
Most PHP distributions include the PostgreSQL extension PDO_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 the php.ini file to uncomment the following line:
1 | ;extension=php_pdo_pgsql.dll |
You just need to remove the semicolon (;) at the beginning of the of the line and restart the web server.
1 | extension=php_pdo_pgsql.dll |
Create PHP project structure with Composer
The Composer is a tool for managing dependency that allows us 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 web root folder to store the project files.
Next, create the app folder and a new composer.json file in the postgresqlphpconnect folder with the following content:
1 2 3 4 5 6 7 | { "autoload": { "psr-4": { "PostgreSQLTutorial\\": "app/" } } } |
It means that every class that we create in the app folder will map to the PostgreSQLTutorial namespace.
Then, go to the command line, navigate to the postgresqlphpconnect folder, and type the following command:
1 | >composer update |
This command instructs the Composer to download the declared libraries in the composer.json file and generate an autoload file. The command will also place all third party library in the newly created vendor folder. Because we didn’t declare any library in the composer.json file, it just generates the autoload file.
1 2 3 4 | Loading composer repositories with package information Updating dependencies (including require-dev) Nothing to install or update Generating autoload files |
After that, create the index.php file in the postgresqlphpconnect folder.
Finally, create two more files in the app folder: Connection.php and database.ini.
The project structure now looks like the following picture.
Connect to the PostgreSQL database
First, create a new database named stocks for the demonstration.
1 | CREATE DATABASE stocks; |
Next, use the database.ini file to store the PostgreSQL database parameters as follows:
1 2 3 4 5 | host=localhost port=5432 database=stocks user=postgres password=postgres |
Then, create a new class named Connection in the Connection.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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <?php namespace PostgreSQLTutorial; /** * 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() { // read parameters in the ini configuration file $params = parse_ini_file('database.ini'); if ($params === false) { throw new \Exception("Error reading database configuration file"); } // connect to the postgresql database $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 */ public static function get() { if (null === static::$conn) { static::$conn = new static(); } return static::$conn; } protected function __construct() { } private function __clone() { } private function __wakeup() { } } |
How it works.
- The
Connectionclass is a singleton class. It means that you can create only one instance for the class. If an instance already exists and you try to create a new one, the class returns the reference to the old one. - To connect to a PostgreSQL database, you need to create a new instance of the PDO class. In the
connect()method, we read the database configuration parameters in thedatabase.inifile, construct a connection string, and pass it to thePDOconstructor.
After that, place the following code in the index.php file.
1 2 3 4 5 6 7 8 9 10 11 12 | <?php require '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, we need to place the code of creating a new PDO object inside the try catch block to handle the exception.
Run the following composer command to update the autoload files.
1 2 | >composer dump-autoload -o Generating optimized autoload files |
Finally, launch the index.php file from the web browser to test it.
1 | 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 the database.ini file to an invalid one and test it.
The following is the error message when the password is invalid.
1 | SQLSTATE[08006] [7] FATAL: password authentication failed for user "postgres" |
And the following is the error message when the database is invalid.
1 | 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 PDO API. We will reuse the Connection class in the subsequent tutorials.