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.

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);
        $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', '', 'fdg3rgdf3435fhgfvgdf')" . 
        ", ('yahoo', '', '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);

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.



Stuart is a software developer with a passion for Linux and open source projects.

comments powered by Disqus
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to and affiliated sites. More info.