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

Working with Resultsets

Upon successful execution of a query, pg_exec() will return a resultset identifier, through which we can access the resultset. The resultset stores the result of the query as returned by the database. For example, if a selection query were executed, the resultset would contain the resulting rows.

PHP offers a number of useful functions for working with resultsets. All of them take a resultset identifier as an argument, so they can only be used after a query has been successfully executed. We learned how to test for successful execution in the previous section.

pg_numrows() and pg_numfields()

Now we'll start with the two simplest result functions: pg_numrows() and pg_numfields(). These two functions return the number of rows and the number of fields in the resultset, respectively. For example:

<?php      
$db_handle = pg_connect("dbname=bpsimple");      
$query = "SELECT * FROM customer";      
$result = pg_exec($db_handle, $query);      
if ($result) {      
   echo "The query executed successfully.<br>\n";      
   echo "Number of rows in result: " . pg_numrows($result) . "<br>\n";      
   echo "Number of fields in result: " . pg_numfields($result);      
} else {      
   echo "The query failed with the following error:<br>\n";      
   echo pg_errormessage($db_handle);      
}      
pg_close($db_handle);      
?>

These functions will return -1 if there is an error.

pg_cmdtuples()

There's also the pg_cmdtuples() function, which will return the number of rows affected by the query. For example, if we were performing insertions or deletions with our query, we wouldn't actually be retrieving any rows from the database, so the number of rows or fields in the resultset would not be indicative of the query's result. Instead, the changes take place inside of the database. pg_cmdtuples() will return the number of rows that were affected by these types of queries (in other words the number of rows inserted, deleted, or updated):

<?php      
$db_handle = pg_connect("dbname=bpsimple");      
$query = "DELETE FROM item WHERE cost_price > 10.00";      
$result = pg_exec($db_handle, $query);      
if ($result) {      
   echo "The query executed successfully.<br>\n";      
   echo "Number of rows deleted: " . pg_cmdtuples($result);      
} else {      
   echo "The query failed with the following error:<br>\n";      
   echo pg_errormessage($db_handle);      
}      
pg_close($db_handle);      
?>

The pg_cmdtuples() function will return 0 if no rows in the database were affected by the query, as in the case of a selection query.