Connecting to PostgreSQL

Summary: in this tutorial, you will learn how to connect to a PostgreSQL database server using PHP PDO.

Prerequisites

To make a connection to the PostgreSQL database server using PHP PDO, you need to have:

• A PostgreSQL database server, a database, and an account with a username and password that can access the database.
• PHP PDO PostgreSQL driver enabled in the php.ini file.

1) PostgreSQL database parameters

Suppose you have the following PostgreSQL database parameters:

• A PostgreSQL database server on the localhost .
• The dvdrental sample database.
• The account with the user postgres and password postgres that can access the dvdrental database on the local database server

The following creates a new database configuration file named config.php that stores the PostgreSQL database parameters:

<?php

$host= 'localhost';
$db = 'dvdrental';
$user = 'postgres';
$password = 'postgres'; // change to your passwordCode language: HTML, XML (xml)

To use these parameters, you include the config.php file in the script that connects to the PostgreSQL using the require construct.

2) Enable PostgreSQL driver

The PDO_PGSQL is a driver that implements the PDO interface. It allows you to access PostgreSQL databases from PHP.

To check if the PDO_PGSQL driver is enabled, you open the php.ini file. Typically, the php.ini file is located under the php directory. For example, if you use XAMPP on Windows, you can find the php.ini file under the C:\xampp\php directory.

in the php.ini file, you can find the following line:

;extension=php_pdo_pgsql.dll

If you see the comma(;) placed at the beginning of the line, it means that the line is commented and the database driver is not enabled.

To enable the driver, you need to uncomment the line by removing the comma (;) like this:

extension=php_pdo_pgsql.dll

After that, you need to restart the web server to apply the change.

PostgreSQL data source name

The data source name or DSN holds database parameters that enable access to a database system. The data source name of the PostgreSQL consists of the following parameters:

• pgsql: is the DNS prefix.
• host: the host of the server where the PostgreSQL runs.
• port: the port to which PostgreSQL listens. The default PostgreSQL’s port is 5432.
• dbname: the database name that you want to connect to.
• And other parameters.

The following shows a DSN that connects to dvdrental database in the local PostgreSQL Server, port 5432:

pgsql:host=localhost;port=5432;dbname=dvdrental;

Connecting to PostgreSQL

The following illustrates how to connect to the dvdrental database in PostgreSQL database server:

<?php

require_once 'config.php';

try {
	$dsn = "pgsql:host=$host;port=5432;dbname=$db;";
	// make a database connection
	$pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

	if ($pdo) {
		echo "Connected to the $db database successfully!";
	}
} catch (PDOException $e) {
	die($e->getMessage());
} finally {
	if ($pdo) {
		$pdo = null;
	}
}
Code language: HTML, XML (xml)

How the script works.

• First, make a new connection to the PostgreSQL database by creating a new instance of the PDO class.
• Second, show a message if the database connection is established successfully; otherwise, show an error message.

The following option instruct PDO to set an error code and throw an exception if an error occurs:

[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]Code language: PHP (php)

PDO has three exception handling options:

• PDO::ERROR_SILENT – PDO sets an error code for inspecting using the PDO::errorCode() and PDO::errorInfo() methods. The PDO::ERROR_SILENT is the default mode.
• PDO::ERRMODE_WARNING – Besides setting the error code, PDO will issue an E_WARNING message.
• PDO::ERRMODE_EXCEPTION – Besides setting the error code, PDO will raise a PDOException.

Note that PHP automatically closes the database connection when the script ends. If you want to close the database connection explicitly, you can set the PDO instance to null:

<?php

$pdo = null;Code language: HTML, XML (xml)

The following connect.php script defines a connect() function that makes a connection to the PostgreSQL database and returns an instance of the PDO class:

<?php

require_once 'config.php';

function connect(string $host, string $db, string $user, string $password): PDO
{
	try {
		$dsn = "pgsql:host=$host;port=5432;dbname=$db;";

		// make a database connection
		return new PDO(
			$dsn,
			$user,
			$password,
			[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
		);
	} catch (PDOException $e) {
		die($e->getMessage());
	}
}

return connect($host, $db, $user, $password);Code language: HTML, XML (xml)

To use this connect.php in a script, you use the require construct as follows:

<?php

$pdo = require 'connect.php';

// start working with the databaseCode language: HTML, XML (xml)

Summary

• Enable PostgreSQL extension in php.ini file by removing the semicolon (;) from the line extension=php_pdo_pgsql.dll
• Create a new instance of PDO by passing the data source name (DSN) to its constructor to make a connection to the PostgreSQL database server.