Create a File in PHP and PostgreSQL for Connection Example

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

PHP is a general-purpose computer script programming language and interpreter suitable for use with web applications and used primarily on Linux servers. PHP, originally derived from Personal Home Page Tools but now standing for “hypertext preprocessor,” is commonly described as recursive acronym. PostgreSQL is an open source object-relational database management system used to create databases and tables for managing information. SQL stands for “structured query language” and is the standard language used for relational database management systems. As PHP requires a database to store information, connecting PHP and the PostgreSQL database makes for dynamic and robust web applications. This tutorial will provide explanations and instructions for executing a PHP and PostgreSQL connection example.

Prerequisites

  • Both PHP and PostgreSQL, must be properly installed on the local system in order to execute the PHP and PostgreSQL connection example in this tutorial. However, the Bitnami WAPP Stack can be used as it includes both PHP and PostgreSQL.

  • A good working knowledge of PHP and PostgreSQL is needed in order to follow the instructions and execute the PHP and PostgreSQL connection example in this tutorial.

PHP and PostgreSQL Connection

Following are two methods used for connecting PHP to the PostgreSQL database cluster:

  • The PostgreSQL Native Function

  • PHP Data Objects, or PDO.

PostgreSQL Native Function

To use the first method, execute the following commands to connect PHP to the PostgreSQL database cluster using the native function:

1
2
3
4
5
6
7
8
9
10
// Set the credentials
-?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// Create connection
$db_conn = pg_connect("host = $hostname dbname = $dbname user = $username password = $pass");
?-

A breakdown of the above command begins with using the pg_connect function to first open a PostgreSQL connection and then specifying the user information of the host name, the database name, the username and password.

PDO (PHP Data Objects)

To use the second method, execute the following commands to connect PHP to the PostgreSQL database cluster using the PHP data objects function:

1
2
3
4
-?php
//Create connection
$db_conn = new PDO('pgsql: host = localhost; dbname = db', 'postgres', 'password');
?-

Breaking down this command begins with using the new PDO function to open the PostgreSQL connection and then specifying the host name, the database name, the username and the password.

Select Data from the Database

With PHP and the PostgreSQL database cluster now connected, retrieve data from the database by executing the following pg_query function on the specific database connection:

1
2
3
4
5
6
7
8
9
10
11
-?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// Create connection
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");

$result = pg_query($db_conn, " SELECT * FROM table_name; ");
?-

Alternatively, execute the following commands for connecting with the PDO method:

1
2
3
4
5
6
-?php
//Create connection
$db_conn = new PDO( 'pgsql: host = localhost; dbname = db', 'postgres', 'password' );

$result = $myPDO--query("SELECT * FROM table_name");
?-

PostgreSQL and PHP Connection Stop

When finished, execute the following pg_close function to shut down the connection:

1
2
3
4
5
6
7
8
9
10
11
-?php
$hostname = "localhost";
$dbname = "db";
$username = "postgres";
$pass = "password";

// Create connection
$db_conn = pg_connect(" host = $hostname dbname = $dbname user = $username password = $pass ");

pg_close($db_conn);
?-

Conclusion

This tutorial provided explanations and instructions for executing a PHP and PostgreSQL connection example. The tutorial gave instruction for establishing a connection to PHP and PostgreSQL database cluster using both the PostgreSQL Native function and the PHP data objects function with a detailed breakdown of the commands used for both functions. The article then explained how to select data from the database and then how to close the Postgres and PHP connection. Remember that while either the PostgreSQL Native function or the PHP data objects function can be used to establish a connection to PHP and the PostgreSQL database cluster, the same user information of the host name, the database name, the username and the password must be entered to retrieve information from the database cluster.

Pilot the ObjectRocket Platform Free!

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

Get Started

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.