PHP Connection to PostgreSQL

PHP Connection to PostgreSQL

This guide shows how to connect a PHP application hosted on the platform to a PostgreSQL database. The workflow includes creating the environment, enabling the PostgreSQL PHP extension, configuring the connection string, checking connectivity, and running a simple database query.

In this article

Create the Environment

1

Create a PHP and PostgreSQL environment

Log in to the platform dashboard and create a new environment with a PHP application server, such as Apache PHP, together with a PostgreSQL database.

Create PHP environment with PostgreSQL
Create a PHP application environment with a PostgreSQL database.
2

Check the database credentials

After environment creation, check the email sent by the platform. It contains the PostgreSQL login and password required to access the database.

PostgreSQL credentials email
Use the PostgreSQL credentials supplied by the platform.

Once the database is ready, it can be accessed through its web administration panel and connected to the PHP application.

Configure the PHP Server

3

Open Apache configuration

Click Config for the Apache PHP application server.

Apache PHP Config button
Open the configuration-file manager for the Apache PHP node.
4

Enable the PostgreSQL PHP extension

Navigate to the etc folder, open php.ini, and add:

extension=pgsql.so
Enable PostgreSQL PHP module
Add the pgsql extension to php.ini.
5

Restart Apache nodes

Save the configuration changes and click Restart Nodes for the Apache server layer.

Restart Apache PHP nodes
Restart the Apache nodes to activate the PostgreSQL extension.

Configure the PostgreSQL Connection

Use the PHP pg_connect() function to open the PostgreSQL connection:

pg_connect("host={host} port={port} dbname={dbname} user={user} password={password}");
{host}PostgreSQL server hostname received by email, without http://. Example: node171206-php-postgresql.jelastic.com.
{port}PostgreSQL connection port. The documented default is 5432.
{dbname}Name of the database to connect to.
{user}Database account username. The source example uses the default webadmin account.
{password}Password for the selected PostgreSQL database user.

Close the PostgreSQL connection with:

pg_close();
i

PHP pages that use the database

The required connection functions must be included in every PHP page that needs PostgreSQL access, either directly or through a shared include file.

Connection Check Up

Use a simple PHP page to test the PostgreSQL connection:

<?php
$dbconn = pg_connect(
    "host=postgres.jelastic.com port=5432 dbname=postgres user=webadmin password=passw0rd"
);

if (!$dbconn) {
    echo "<center><h1>Doesn't work =(</h1></center>";
} else {
    echo "<center><h1>Good connection</h1></center>";
}

pg_close($dbconn);
?>

Replace the sample values with the actual PostgreSQL hostname, database name, username, and password before testing.

Execute a Simple PostgreSQL Query

The following example connects to PostgreSQL, runs a query against test_table, and displays the first two values from each returned row:

<?php
$conn = pg_connect(
    "host=postgres.jelastic.com 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";
}
?>

These connection and query examples can be adapted for a PHP application that uses PostgreSQL as its database.

Expected Result

The Apache PHP server has PostgreSQL support enabled through pgsql.so. The PHP application can establish a connection using pg_connect(), execute PostgreSQL queries with pg_query(), process rows with pg_fetch_row(), and close the connection with pg_close().

Important Notes

  • Enable extension=pgsql.so in php.ini before using PostgreSQL functions.
  • Restart the Apache PHP nodes after changing php.ini.
  • Use the PostgreSQL host without http:// or https://.
  • The documented default PostgreSQL port is 5432.
  • The source example uses webadmin as the database user.
  • Replace all sample hostnames and credentials before using the example code.