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.
Last updated: 15th August 2020
First published: 16th August 2018
First published: 16th August 2018