Summary: this tutorial shows you 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 account with username and password that can access the database.
- PHP PDO PostgreSQL driver enabled in your web server.
For example, we have a local PostgreSQL database server that has pagila sample database and an account with postgres username and postgres password which can access the pagila database. We can create a new database configuration file named dbconfig.php and put these database parameters into the file as shown below:
1 2 3 4 5 | <?php $host='localhost'; $db = 'pagila'; $username = 'postgres'; $password = 'postgres'; |
Later on, we will use the dbconfig.php configuration file in other script files that need to access this configuration parameters by using the require_once() function.
To check if the PDO 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.
1 | 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 system. The data source name of the PostgreSQL is composed of the following parameters:
- DNS prefix:
pgsql: - host: the database server’s host name 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 user that connects to the database
dbname. You can specify the user name in either DSN or in 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 pagila database in the local PostgreSQL database server.
1 | pgsql:host=localhost;port=5432;dbname=pagila;user=postgres;password=postgres |
Connecting to PostgreSQL
The following code illustrates how to connect to the pagila database in PostgreSQL database server:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?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(); } |
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 you create a new connection object, you pass the DSN as an argument to its parameter.
- The
try catchstatement 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:
1 | Connected to the pagila database successfully! |
If you are using WAMP server, after enabling the POD PostgreSQL driver, it is still not working and you got the following error message:
1 | could not find driver |
You can put the following line:
1 | LoadFile "c:/wamp/bin/php/php5.4.3/libpq.dll" |
At the end of the httpd.conf file in the C:\wamp\bin\apache\apache2.2.22\conf folder.
In this tutorial, we have shown you step by step how to connect to the PostgreSQL database server using PHP PDO.