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 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

Ben Ramsden

Sharp PN-40TC1 Huddle Board

Brainstorming, innovation, problem solving, and negotiation have all become much more productive and valuable if people can easily collaborate in real time with minimal friction.

Sarah Ieroianni

Brother QL-820NWB Professional Label Printer

The print quality also does not disappoint, it’s clear, bold, doesn’t smudge and the text is perfectly sized.

Ratchada Dunn

Sharp PN-40TC1 Huddle Board

The Huddle Board’s built in program; Sharp Touch Viewing software allows us to easily manipulate and edit our documents (jpegs and PDFs) all at the same time on the dashboard.

George Khoury

Sharp PN-40TC1 Huddle Board

The biggest perks for me would be that it comes with easy to use and comprehensive programs that make the collaboration process a whole lot more intuitive and organic

David Coyle

Brother PocketJet PJ-773 A4 Portable Thermal Printer

I rate the printer as a 5 out of 5 stars as it has been able to fit seamlessly into my busy and mobile lifestyle.

Kurt Hegetschweiler

Brother PocketJet PJ-773 A4 Portable Thermal Printer

It’s perfect for mobile workers. Just take it out — it’s small enough to sit anywhere — turn it on, load a sheet of paper, and start printing.

Featured Content

Product Launch Showcase

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?