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)
   exit("Could not connect to PostgreSQL");

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

$res = pg_exec($conn,"SELECT * FROM test;");
if(!$res)
   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++) {
     list($a,$b,$c) = pg_fetch_row($row,$i);
     echo "Row $i: a = $a, b = $b, c = $c\n";
   }
}
$res = pg_exec($conn,"SELECT count(*) as c from test where a=1;");
if($res) {
   $count = pg_result($res,0,"c");
   echo "Rows where a=1: $count\n";
}
$res = pg_exec($conn,"SELECT * FROM test;");
for($i=0;$i<pg_numrows($res);$i++) {
   for($j=0;$j<pg_numfields($res);$j++) {
   echo pg_fieldname($res,$j)."
".pg_result($res,$i,pg_fieldname($res,$j))."\n";
   }
   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().

Recommended

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

Gavin Sherry

PC World

Comments

Comments are now closed.

Most Popular Reviews

Follow Us

Best Deals on GoodGearGuide

Shopping.com

Latest News Articles

Resources

GGG Evaluation Team

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.

Steph Mundell

LIFEBOOK UH574

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

STYLISTIC Q702

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

STYLISTIC Q702

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.

Latest Jobs

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?