PDO Connecting to PostgreSQL

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

Prerequisites

Before connecting to the PostgreSQL database server using PHP PDO, you need to have:

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

For example:

  • A local PostgreSQL database server
  • The dvdrental sample database
  • An account with postgres username and postgres password which can access the dvdrental database.

The following creates a new database configuration file named dbconfig.php and place these parameters into the configuration file:

<?php $host='localhost'; $db = 'dvdrental'; $username = 'postgres'; $password = 'postgres'; // change to your password
Code language: HTML, XML (xml)

From now, you can include the dbconfig.php configuration file in other script files that need to connect to the PostgreSQL database server by using the require_once() function.

To check if the PostgreSQL driver is enabled, you open the php.ini file and check if the following line is un-commented. If it is not, you can remove the semicolon ( ;) in front of the entry.

extension=php_pdo_pgsql.dll

PostgreSQL data source name

The data source name or DSN conveys the database parameters that allow you to connect to a database system. PDO defines different DSN for different database systems.

The data source name of the PostgreSQL consists of the following parameters:

  • DNS prefix : pgsql:
  • host: the database server’s hostname where the PostgreSQL database locates.
  • port: the port which PostgreSQL database is running, the default port is 5432.
  • dbname: database name.
  • user: The name of the user that connects to the database dbname. You can specify the user name in either DSN or in the constructor of the PDO class.
  • password: The password of the user name. You can specify the password in either the DSN or the PDO constructor.

Notice that PDO ignores the username and password in the PDO constructor if you put them in the data source name (DSN).

The following is the DSN that allows us to connect to dvdrental database in the local PostgreSQL database server.

pgsql:host=localhost;port=5432;dbname=dvdrental;user=postgres;password=postgres

Connecting to PostgreSQL

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

<?php require_once 'dbconfig.php'; $dsn = "pgsql:host=$host;port=5432;dbname=$db;user=$username;password=$password"; try{ // create a PostgreSQL database connection $conn = new PDO($dsn); // display a message if connected to the PostgreSQL successfully if($conn){ echo "Connected to the <strong>$db</strong> database successfully!"; } }catch (PDOException $e){ // report error message echo $e->getMessage(); }
Code language: HTML, XML (xml)

How the script works.

  • To connect to a PostgreSQL database, you just need to create a new connection object, which is an instance of the PDO class. When creating a new connection object, you pass the DSN as an argument to its parameter.
  • The try...catch statement is used to catch any exception that may occur during connecting to the PostgreSQL database. In the catch block, we display the error message if there is anything wrong with the connection.

If you have everything setup correctly, you will see the following message:

Connected to the dvdrental database successfully!

In this tutorial, you have learned how to connect to the PostgreSQL database server using PHP PDO.

Did you find this tutorial useful?