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.
Rocket to Success - Your 10 Tips for Smarter ERP System Selection
Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

Gavin Sherry

PC World
Show Comments

Most Popular Reviews

Latest Articles


PCW Evaluation Team

Matthew Stivala

HP OfficeJet 250 Mobile Printer

The HP OfficeJet 250 Mobile Printer is a great device that fits perfectly into my fast paced and mobile lifestyle. My first impression of the printer itself was how incredibly compact and sleek the device was.

Armand Abogado

HP OfficeJet 250 Mobile Printer

Wireless printing from my iPhone was also a handy feature, the whole experience was quick and seamless with no setup requirements - accessed through the default iOS printing menu options.

Azadeh Williams

HP OfficeJet Pro 8730

A smarter way to print for busy small business owners, combining speedy printing with scanning and copying, making it easier to produce high quality documents and images at a touch of a button.

Andrew Grant

HP OfficeJet Pro 8730

I've had a multifunction printer in the office going on 10 years now. It was a neat bit of kit back in the day -- print, copy, scan, fax -- when printing over WiFi felt a bit like magic. It’s seen better days though and an upgrade’s well overdue. This HP OfficeJet Pro 8730 looks like it ticks all the same boxes: print, copy, scan, and fax. (Really? Does anyone fax anything any more? I guess it's good to know the facility’s there, just in case.) Printing over WiFi is more-or- less standard these days.

Ed Dawson

HP OfficeJet Pro 8730

As a freelance writer who is always on the go, I like my technology to be both efficient and effective so I can do my job well. The HP OfficeJet Pro 8730 Inkjet Printer ticks all the boxes in terms of form factor, performance and user interface.

Michael Hargreaves

Windows 10 for Business / Dell XPS 13

I’d happily recommend this touchscreen laptop and Windows 10 as a great way to get serious work done at a desk or on the road.

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?