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');

function getConnection($host, $db_name, $user, $password, $port="5432", $use_utf8=true, $force_new=false, $useAsync=false)
{
    if ($force_new && $useAsync)
    {
        $force_new = false;
    }

    $connString = 
        "host=" . $host 
        . " dbname=" . $db_name 
        . " user=" . $user 
        . " password=" . $password 
        . " port=" . $port;

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

    if ($useAsync)
    {
        $connection = pg_connect($connString, PGSQL_CONNECT_ASYNC);
    }
    elseif ($force_new)
    {
        $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.