PHP and PostgreSQL

PHP and PostgreSQL, Part II

Since PostgreSQL is based on a client/server principle, applications must first connect or 'bind' to the server before being able to interact with it.

$conn = pg_connect("user=postgres dbname=phphh");

This short script connects to a PostgreSQL database called 'phphh' with username 'postgres'. Note that pg_connect() takes a single argument, a connection string, which consists of name=value pairs, in much the same way that PHP takes URL encoded arguments. In the case of the pg_connect() connection string, the delimiter is a space, not an ampersand. Other valid connection string names include:

host - an optional host name for the database
port - an optional port number for the database
password - a password for the user you are connecting as
options - options to pass to the backend
tty - a UNIX terminal path to send debugging information to

Unless you have customised your PostgreSQL installation, you should not need to use these.

The pg_connect() function returns a database connection handle which should be used with the PostgreSQL utility functions. On error, it returns FALSE.

Persistent connections

PHP provides a function very similar to pg_connect() - pg_pconnect(). It takes the same connection string argument and returns a database connection handle, but does a little more. It is a 'persistent' interface to the underlying connection handling system, meaning that when a connection is made it is not closed, even when execution of the script has finished.

Query execution

Once you have connected to the server, you can execute SQL queries with the pg_exec() function. The following script shows how.

$conn = pg_pconnect("user=postgres dbname=phphh");
 &nbsp exit("Could not connect to PostgreSQL");

$res = pg_exec($conn,"INSERT INTO test
 &nbsp exit("Insert failed");

$res = pg_exec($conn,"SELECT * FROM test;");
 &nbsp exit("Could not select from test");

This short script connects to the database server, inserts some data into a table 'test' and then retrieves the data. Note that the script tests the validity of the result handle returned by pg_exec(). If pg_exec() returns FALSE, the query has failed.

Retrieving data

In the example discussed above, there are two kinds of queries: an insert, which entails storage of data; and a select, which involves retrieval of data. PHP and PostgreSQL provide a host of functions to access results of a select:
Primary Functions
pg_fetch_row() - retrieve a single row from the result set
pg_fetch_array() - retrieve a single row from the result set as an array
pg_fetch_object() - retrieve a single row from the result set as an object
pg_result() - retrieve a single field from a result set

Helper Functions
pg_numrows() - number of rows returned by the query
pg_numfields() - number of fields per row returned by query
pg_fieldnum() - the numeric number of a given field
pg_fieldname() - the name of a given field

This script illustrates some uses of these functions:
$conn = pg_pconnect("user=postgres dbname=phphh");
$res = pg_exec($conn,"SELECT a,b,c FROM test;");
$c = pg_numrows($res);
if($c) {
   for($i=0;$i<$c;$i++) {
 &nbsp &nbsp list($a,$b,$c) = pg_fetch_row($row,$i);
 &nbsp &nbsp echo "Row $i: a = $a, b = $b, c = $c\n";
 &nbsp }
$res = pg_exec($conn,"SELECT count(*) as c from test where a=1;");
if($res) {
 &nbsp $count = pg_result($res,0,"c");
 &nbsp echo "Rows where a=1: $count\n";
$res = pg_exec($conn,"SELECT * FROM test;");
for($i=0;$i<pg_numrows($res);$i++) {
 &nbsp for($j=0;$j<pg_numfields($res);$j++) {
 &nbsp echo pg_fieldname($res,$j)."
 &nbsp }
 &nbsp echo "\n--\n";

With the first query, the script retrieves the results with the pg_fetch_row() function. It returns a single row at a time, so we loop through the result set up to pg_numrows() rows. The function returns an array of values corresponding to the columns that we select from the table 'test' - columns a, b and c. We list() these and output them.

The script then executes a 'qualified select'. That is, it limits the retrieval to only certain rows - those where column 'a' is equal to 1. The query uses the aggregate function count() to count the number of rows (it reduces a result set to a single function).

The result of this second query is then retrieved with the pg_result() function. Notice that the third argument to this function is 'c', the name of the result we want.

Finally, the script retrieves all data from the table and sorts through it field by field, by building two loops: the outer looping vertically through the result set and the inner looping horizontally. We do this with the help of pg_numfields() and pg_numrows().

Join the PC World newsletter!

Error: Please check your email address.

Our Back to Business guide highlights the best products for you to boost your productivity at home, on the road, at the office, or in the classroom.

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Gavin Sherry

PC World
Show Comments

Cool Tech

Crucial Ballistix Elite 32GB Kit (4 x 8GB) DDR4-3000 UDIMM

Learn more >

Gadgets & Things

Lexar® Professional 1000x microSDHC™/microSDXC™ UHS-II cards

Learn more >

Family Friendly

Lexar® JumpDrive® S57 USB 3.0 flash drive 

Learn more >

Stocking Stuffer

Plox Star Wars Death Star Levitating Bluetooth Speaker

Learn more >

Christmas Gift Guide

Click for more ›

Most Popular Reviews

Latest News Articles


GGG Evaluation Team

Kathy Cassidy


First impression on unpacking the Q702 test unit was the solid feel and clean, minimalist styling.

Anthony Grifoni


For work use, Microsoft Word and Excel programs pre-installed on the device are adequate for preparing short documents.

Steph Mundell


The Fujitsu LifeBook UH574 allowed for great mobility without being obnoxiously heavy or clunky. Its twelve hours of battery life did not disappoint.

Andrew Mitsi


The screen was particularly good. It is bright and visible from most angles, however heat is an issue, particularly around the Windows button on the front, and on the back where the battery housing is located.

Simon Harriott


My first impression after unboxing the Q702 is that it is a nice looking unit. Styling is somewhat minimalist but very effective. The tablet part, once detached, has a nice weight, and no buttons or switches are located in awkward or intrusive positions.

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?