PostgreSQL - Connect with PHP

Learn how to connect to a PostgreSQL database instance with one of the world's most famous programming languages: PHP.

Managed Databases (also called Cloud Databases) allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

You can find an example in the GitHub examples repository.

Requirements

• access to the OVHcloud Control Panel
• a Public Cloud project in your OVHcloud account
• a PostgreSQL database running on your OVHcloud Public Cloud Databases (this guide can help you to meet this requirement)
• configure your PostgreSQL instance to accept incoming connections
• a PHP environment with a stable version and public network connectivity (Internet).
  This guide was made in PHP 8.0.8.

Concept

A PostgreSQL instance can be managed in multiple ways. One of the easiest, yet powerful, is to use a Command Line Interface (CLI), as shown in our guide: Connect to PostgreSQL with CLI.

Another way is to interact directly using programming languages, such as PHP. PHP is used in almost 80% of the websites in the world, such as Facebook, Wikipedia, or WordPress. PostgreSQL provides PHP drivers, allowing us to connect and manage a PostgreSQL instance from code.

To do so, we will need to set up our PHP environment with PostgreSQL drivers, then configure our Public Cloud Databases for PostgreSQL instances to accept incoming connections, and finally code in PHP to perform a few example actions.

Instructions

Setup your PHP environment

To interact with your PostgreSQL instance with PHP, your development environment needs to be configured with:

• A compatible version of PHP;
• PostgresSQL and PDO PHP extension;

Please follow the official PostgreSQL to get the latest information.

If you can edit your PHP environment on your own, install extensions and libraries as detailed in the documentation page linked above.

We are now ready to learn how to connect to our PostgreSQL instance.

Connect with PHP

Using pg_connect

<?php
$host = "postgresql-6xxxxxxx-ocxxxxxc5.database.cloud.ovh.us";
$user = "avnadmin";
$password = "NlxxxxxxxxxxxxxxxjYv";
$dbname = "defaultdb";
$port = "20235";

$dbconn = pg_connect("host=$host dbname=$dbname user=$user password=$password port=$port sslmode=require")
    or die('Could not connect: ' . pg_last_error());

$stat = pg_connection_status($dbconn);
if($stat === PGSQL_CONNECTION_OK){
  echo 'Connection OK';
} else {
  echo 'An error occurred';
}
?> 

If you are connected, the result should be like this:

<?php
$host = "postgresql-6xxxxxxx-ocxxxxxc5.database.cloud.ovh.us";
$user = "avnadmin";
$password = "NlxxxxxxxxxxxxxxxjYv";
$dbname = "defaultdb";
$port = "20235";

$conn = new PDO("pgsql:host=$host; port=$port; dbname=$dbname; sslmode=require; user=$user; password=$password");
var_dump($conn->query("select datname from pg_database;")->fetchAll());
?>

If you are connected, the result should be like this:

array(4) {
  [0]=>
  array(2) {
    ["datname"]=>
    string(9) "template1"
    [0]=>
    string(9) "template1"
  }
  [1]=>
  array(2) {
    ["datname"]=>
    string(9) "template0"
    [0]=>
    string(9) "template0"
  }
  [2]=>
  array(2) {
    ["datname"]=>
    string(6) "_aiven"
    [0]=>
    string(6) "_aiven"
  }
  [3]=>
  array(2) {
    ["datname"]=>
    string(9) "defaultdb"
    [0]=>
    string(9) "defaultdb"
  }
}

Go further

For more information and tutorials, please see our other Cloud Databases support guides or explore the guides for other OVHcloud products and services.