Beginning Databases with PostgreSQL - Chapter 15: Accessing PostgreSQL from PHP

Extracting Values from Resultsets

There are a number of ways to extract values from resultsets. We will start with the pg_result() function.

pg_result()

The pg_result() function is used when you want to retrieve a single value from a resultset. In addition to a resultset identifier, you must also specify the row and field that you want to retrieve from the result. The row is specified numerically, while the field may be specified either by name or by numeric index. Numbering always starts at zero.

Here's an example using pg_result():

<?php        
$db_handle = pg_connect("dbname=bpsimple");        
$query = "SELECT title, fname, lname FROM customer";        
$result = pg_exec($db_handle, $query);        
if ($result) {        
   echo "The query executed successfully.<br>\n";        
   for ($row = 0; $row < pg_numrows($result); $row++) {        
       $fullname = pg_result($result, $row, 'title') . " ";        
       $fullname .= pg_result($result, $row, 'fname') . " ";        
       $fullname .= pg_result($result, $row, 'lname');        
       echo "Customer: $fullname<br>\n";        
   }        
} else {        
   echo "The query failed with the following error:<br>\n";        
   echo pg_errormessage($db_handle);        
}        
pg_close($db_handle);        
?>

Using numeric indices, this same block of code could also be written like this:

<?php        
$db_handle = pg_connect("dbname=bpsimple");        
$query = "SELECT title, fname, lname FROM customer";        
$result = pg_exec($db_handle, $query);        
if ($result) {        
   echo "The query executed successfully.<br>\n";        
   for ($row = 0; $row < pg_numrows($result); $row++) {        
       $fullname = "";        
       for ($col = 0; $col < pg_numfields($result); $col++) {        
           $fullname .= pg_result($result, $row, $col) . " ";        
       }        
       echo "Customer: $fullname<br>\n";        
   }        
} else {        
   echo "The query failed with the following error:<br>\n";        
   echo pg_errormessage($db_handle);        
}        
pg_close($db_handle);        
?>

The first example is a bit more readable, however, and doesn't depend on the order of the fields in the resultset. PHP also offers more advanced ways of retrieving values from resultsets, because iterating through rows of results isn't especially efficient.

pg_fetch_row()

PHP provides two functions, pg_fetch_row() and pg_fetch_array(), that can return multiple result values at once. Each of these functions returns an array.

pg_fetch_row() returns an array that corresponds to a single row in the resultset. The array is indexed numerically, starting from zero. Here is the previous example rewritten to use pg_fetch_row():

<?php        
$db_handle = pg_connect("dbname=bpsimple");        
$query = "SELECT title, fname, lname FROM customer";        
$result = pg_exec($db_handle, $query);        
if ($result) {        
   echo "The query executed successfully.<br>\n";        
   for ($row = 0; $row < pg_numrows($result); $row++) {        
       $values = pg_fetch_row($result, $row);        
       $fullname = "";        
       for ($col = 0; $col < count($values); $col++) {        
           $fullname .= $values[$col] . " ";        
       }        
       echo "Customer: $fullname<br>\n";        
   }        
} else {        
   echo "The query failed with the following error:<br>\n";        
   echo pg_errormessage($db_handle);        
}        
pg_close($db_handle);        
?>

As you can see, using pg_fetch_row() eliminates the multiple calls to pg_result(). It also places the result values in an array, which can be easily manipulated using PHP's native array functions.

In this example, However, we are still accessing the fields by their numeric indices. Ideally, we should also be able to access each field by its associated name. To accomplish that, we can use the pg_fetch_array() function.

pg_fetch_array()

The pg_fetch_array() function also returns an array, but it allows us to specify whether we want that array indexed numerically or associatively (using the field names as keys). This preference is specified by passing one of the following as the third argument to pg_fetch_array():

• PGSQL_ASSOC Index the resulting array by field name
• PGSQL_NUM Index the resulting array numerically
• PGSQL_BOTH Index the resulting array both numerically and by field name

If you don't specify one of the above indexing methods, PGSQL_BOTH will be used by default. Note that this will double the size of your resultset, so you're probably better off explicitly specifying one of the above. Also note, that the field names will always be returned in lower case letters, regardless of how they're represented in the database itself.

Here's the example rewritten once more, now using pg_fetch_array():

<?php        
$db_handle = pg_connect("dbname=bpsimple");        
$query = "SELECT title, fname, lname FROM customer";        
$result = pg_exec($db_handle, $query);        
if ($result) {        
   echo "The query executed successfully.<br>\n";        
   for ($row = 0; $row < pg_numrows($result); $row++) {        
       $values = pg_fetch_array($result, $row, PGSQL_ASSOC);        
       $fullname = $values['title'] . " ";        
       $fullname .= $values['fname'] . " ";        
       $fullname .= $values['lname'];        
       echo "Customer: $fullname<br>\n";        
   }        
} else {        
   echo "The query failed with the following error:<br>\n";        
   echo pg_errormessage($db_handle);        
}        
pg_close($db_handle);        
?>

pg_fetch_object()

PHP also allows you to fetch the result values with the pg_fetch_object() function. Each field name will be represented as a property of this object. Thus, fields cannot be accessed numerically. Written using pg_fetch_object(), our example looks like this:

<?php        
$db_handle = pg_connect("dbname=bpsimple");        
$query = "SELECT title, fname, lname FROM customer";        
$result = pg_exec($db_handle, $query);        
if ($result) {        
   echo "The query executed successfully.<br>\n";        
   for ($row = 0; $row < pg_numrows($result); $row++) {        
       $values = pg_fetch_object($result, $row, PGSQL_ASSOC);        
       $fullname = $values->title . " ";        
       $fullname .= $values->fname . " ";        
       $fullname .= $values->lname;        
       echo "Customer: $fullname<br>\n";        
   }        
} else {        
   echo "The query failed with the following error:<br>\n";        
   echo pg_errormessage($db_handle);        
}        
pg_close($db_handle);        
?>