Programster's Blog

Tutorials focusing on Linux, programming, and open source

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'";
    }
    
    print $connString . PHP_EOL;
    
    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.