Getting Started With Using PostgreSQL in PHP

The first thing you need to do is install PHP and support for PostgreSQL. For ubuntu 16.04 you would need to run:

sudo apt-get install php7.0 php7.0-pgsql -y

If you haven't already installed a PostgreSQL server and configured it with a password and to allow remote connections, then do that now.

The script below demonstrates how to:

• connect to the PostgreSQL server.
• insert some data
• grab some data and loop over the results.

Copy and paste the script into a file called script.php before executing it with php script.php.

<?php

define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_PORT', '5432');
define('DB_USER', 'postgres');
define('DB_PASSWORD', 'my_password');

/**
 * Create a new PostgreSQL database connection
 * @param string $host - the IP or FQDN of where the postgreSQL database is hosted.
 * @param string $dbName - the name of the database.
 * @param string $user - the user to connect with
 * @param string $password - the password for that user.
 * @param int $port - the port to connect on (defaults to postgresql default port)
 * @param int $timeout - the number of seconds to wait for a connection before timing out.
 * @param bool $useUtf8 - whether to use UTF8 encoding (defaults to true)
 * @param bool $forceNew - whether to set connection type to PGSQL_CONNECT_FORCE_NEW, which if passed, then a new
 * connection is created, even if the connection_string is identical to an existing connection.
 * @param bool $useAsync - Whether to set PGSQL_CONNECT_ASYNC. If set then the connection is established
 * asynchronously. The state of the connection can then be checked via pg_connect_poll() or pg_connection_status().
 * @return \PgSql\Connection - the connection to the PostgreSQL database (object added in PHP 8.1)
 * @throws Exception - if there was an issue connecting to the database.
 */
function getConnection(
    string $host,
    string $dbName,
    string $user,
    string $password,
    int    $port = 5432,
    int    $timeout = 5,
    bool   $useUtf8 = true,
    bool   $forceNew = false,
    bool   $useAsync = false
)
{
    if ($forceNew && $useAsync)
    {
        $forceNew = false;
    }

    $connString = 
        "host={$host}" 
        . " dbname={$dbName}"
        . " user={$user}" 
        . " password={$password}" 
        . " port={$port}"
        . " connect_timeout={$timeout}";

    if ($useUtf8)
    {
        $connString .= " options='--client_encoding=UTF8'";
    }

    if ($useAsync)
    {
        $connection = pg_connect($connString, PGSQL_CONNECT_ASYNC);
    }
    elseif ($forceNew)
    {
        $connection = pg_connect($connString, PGSQL_CONNECT_FORCE_NEW);
    }
    else
    {
        $connection = pg_connect($connString);
    }

    if ($connection == false)
    {
        throw new \Exception("Failed to initialize database connection.");
    }

    return $connection;
}

$connection = getConnection(DB_HOST, DB_NAME, DB_USER, DB_PASSWORD);

$createTableQuery = "CREATE TABLE broker (
    id serial NOT NULL,
    name varchar(255) NOT NULL,
    redirect_url text NOT NULL,
    secret varchar(30) NOT NULL,
    modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)";

$result = pg_query($connection, $createTableQuery);

$insertBrokersQuery = 
    "INSERT INTO broker (name, redirect_url, secret) VALUES " . 
        "('google', 'www.google.com', 'fdg3rgdf3435fhgfvgdf')" . 
        ", ('yahoo', 'www.yahoo.com', 'dfdfweregf33434gdfdd')";

$result = pg_query($connection, $insertBrokersQuery);

$selectBrokersQuery = "SELECT * FROM broker";
$result = pg_query($connection, $selectBrokersQuery);

if (!$result) 
{
    echo "An error occurred.\n";
    print pg_last_error($connection);
    exit;
}

while ($row = pg_fetch_assoc($result)) 
{
    print "row: " . print_r($row, true) . PHP_EOL;
}

print "done" . PHP_EOL;

After reading and executing the script, you should see that it's very much like using mysqli with fetched rows having name/value form.