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");
if(!conn)
 &nbsp exit("Could not connect to PostgreSQL");

$res = pg_exec($conn,"INSERT INTO test
VALUES(1,'test1','test2');");
if(!$res)
 &nbsp exit("Insert failed");

$res = pg_exec($conn,"SELECT * FROM test;");
if(!$res)
 &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)."
".pg_result($res,$i,pg_fieldname($res,$j))."\n";
 &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

Essentials

Lexar® JumpDrive® S57 USB 3.0 flash drive

Learn more >

Microsoft L5V-00027 Sculpt Ergonomic Keyboard Desktop

Learn more >

Mobile

Lexar® JumpDrive® S45 USB 3.0 flash drive 

Learn more >

Exec

Lexar® JumpDrive® C20c USB Type-C flash drive 

Learn more >

Audio-Technica ATH-ANC70 Noise Cancelling Headphones

Learn more >

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

Learn more >

HD Pan/Tilt Wi-Fi Camera with Night Vision NC450

Learn more >

Budget

Back To Business Guide

Click for more ›

Most Popular Reviews

Latest News Articles

Resources

PCW Evaluation Team

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.

Aysha Strobbe

Windows 10 / HP Spectre x360

Ultimately, I think the Windows 10 environment is excellent for me as it caters for so many different uses. The inclusion of the Xbox app is also great for when you need some downtime too!

Mark Escubio

Windows 10 / Lenovo Yoga 910

For me, the Xbox Play Anywhere is a great new feature as it allows you to play your current Xbox games with higher resolutions and better graphics without forking out extra cash for another copy. Although available titles are still scarce, but I’m sure it will grow in time.

Kathy Cassidy

STYLISTIC Q702

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

Anthony Grifoni

STYLISTIC Q572

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

Featured Content

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?