How to configure connection to PostgreSQL from your PHP project

by Yulia Shevchenko

PostgreSQL (Postgres) is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

In this step-by-step guide we’ll show how to easily connect your PHP project hosted at Cloudlets Australia cloud provider to the PostgreSQL database server.

Create Your Environment

1. Log into (or register free) the dashboard.

2. Create the environment with the PHP app server (e.g. LiteSpeed Web Server) and the PostgreSQL database.

Wait for a couple of minutes while your environment has been created.

3. You’ll receive an email with the credentials to PostgreSQL database. The next step is accessing the database via web admin panel and connecting it to the PHP project.

Setting up connection to PostgreSQL Database

1. Click the Config button for your LiteSpeed server.

2. Go to the etc folder

Then open the php.ini file.

Add the extension=pgsql.so line. Save the changes.

3. Now you have to restart nodes for LiteSpeed server.

4. There are two main PG functions for operating with a database server:

1) Opening PostgreSQL connection:

pg_connect(“host={host} port={port} dbname={dbname} user={user} password={password}"); where:

  • {host} - the PostgreSQL server Host (i.e. access URL without http://) that you’ve received via email, for example node3433-testing.mel.cloudlets.com.au
  • {port} - a connection port (the default one is 5432)
  • {dbname} - a name of your database
  • {user} - an account name to access database with (we’ll use the default webadmin)
  • {password} - a password for the appropriate user

2) Closing PostgreSQL connection: pg_close()

5. You need to write the necessary functions in every *.php page, which should be connected to the database.

Check the Connection to Your PostgreSQL DataBase

  • check the connection using this code:
<?php  
$dbconn = pg_connect("host=testing.mel.cloudlets.com.au port=5432 dbname=postgres user=webadmin password=passw0rd");  
//connect to a database named "postgres" on the host "host" with a username and password  
if (!$dbconn){  
echo "<center><h1>Doesn't work =(</h1></center>";  
}else  
 echo "<center><h1>Good connection</h1></center>";  
pg_close($dbconn);  
?>
  • execute simple request and output it into table:
<?php  
$conn = pg_connect("host=testing.mel.cloudlets.com.au port=5432 dbname=postgres user=webadmin password=passw0rd");  
if (!$conn) {  
 echo "An error occurred.\n";  
 exit;  
}  
$result = pg_query($conn, "SELECT * FROM test_table");  
if (!$result) {  
 echo "An error occurred.\n";  
 exit;  
}  
while ($row = pg_fetch_row($result)) {  
 echo "value1: $row[0]  value2: $row[1]";  
 echo "<br />\n";  
}  
?>

That’s all!

Now you are aware of how to create your auto scalable PHP projects and easily configure connection to the PostgreSQL database. Try it out at https://cloudlets.com.au/