Client 3Simple ProcessingPQexec() and PQprint()

Client 3Query Processing

The tasks involved in processing a query (or other command) using PHP are similar to those required in other PostgreSQL APIs. The first step is to execute the command; then you can (optionally) process the metadata returned by the command; and finally, you process the result set.

We're going to switch gears here. So far, we have been writing PHP scripts that are proceduralone PHP command follows the next. We've thrown in a couple of functions to factor out some repetitive details (such as establishing a new connection). For the next example, you'll create a PHP class, named my_table, that will execute a command and process the results. You can reuse this class in other PHP scripts; and each time you extend the class, all scripts automatically inherit the changes.

Let's start by looking at the first script that uses the my_table class and then we'll start developing the class. Listing 15.10 shows client3a.php.

Listing 15.10. client3a.php

 1 

23client3a 4

5 6 finish(); 16 17 pg_close( $db_handle ); 18 19 ?> 20 21

22

I rearranged the code in this client so that the static (that is, unchanging) HTML code is separated from the PHP script; that makes it a little easier to discern the script.

At line 10, client3a includes() the my_table_a.php file. That file contains the definition of the my_table class, and we'll look at it in greater detail in a moment. Line 14 creates a new my_table object named $table. The constructor function for the my_table class expects two parameters: a database handle and a command string. my_table() executes the given command and formats the results into an HTML table. At line 15, the call to my_table->finish() completes the HTML table. Finally, you call pg_close() to close the database connection; that's not strictly necessary, but it's good form.

Listing 15.11 shows my_table_a.php.

Listing 15.11. my_table_a.php

 1 result = pg_query( $db_handle, $command );
 13 $this->columns = pg_num_fields( $this->result );
 14 $row_count = pg_num_rows( $this->result );
15
 16 $this->start_table();
 17
 18 for( $row = 0; $row < $row_count; $row++ )
 19 $this->append_row( $this->result, $row );
 20 }
 21
 22 function start_table()
 23 {
 24 echo ''; 
 25 echo "
";
 26 }
 27
 28 function finish() 
 29 { 
 30 print( "

" ); 31 32 pg_free_result( $this->result ); 33 } 34 35 function append_row( $result, $row ) 36 { 37 echo( " " ); 38 39 for( $col = 0; $col < $this->columns; $col++ ) 40 { 41 echo " "; 42 echo pg_fetch_result( $result, $row, $col ); 43 echo " "; 44 } 45 46 echo( " " ); 47 } 48 } 49 50 ?>

my_table.php defines a single class named my_table. At lines 7 and 8, you see the two instance variables for this class. $this->$result contains a handle to a result set. $this->columns stores the number of columns in the result set.

The constructor for my_table (lines 10 through 20) expects a database handle and a command string. At line 12, the constructor calls the pq_query() function to execute the given command. pg_query() returns a result set handle if successful, and returns FALSE if an error occurs. You'll see how to intercept pg_query() errors in a moment. After you have a result set, you can call pg_num_fields() to determine the number of columns in the result set and pg_num_rows() to find the number of rows.

pg_query() in Earlier PHP Versions

In older versions of PHP, the pg_query() function was named pg_exec(), pg_num_fields() was named pg_numfields(), and pg_num_rows() was named pg_numrows(). If you run into complaints about invalid function names, try the old names.

 

At line 16, the call to the start_table() member function prints the HTML table header. Finally, at lines 18 and 19, the constructor iterates through each row in the result set and calls append_row() to create a new row in the HTML table. We'll look at append_row() shortly.

The start_table() and finish_table() member functions create the HTML table header and table footer, respectively. finish_table() also frees up the resources consumed by the result set by calling pg_free_result().

The append_row() member function starts at line 35. append_row() expects two parameters: a result set handle ($result) and a row number ($row). At line 37, append_row() writes the HTML table-row tag (). The loop at lines 39 through 44 processes each column in the given row. For each column, append_row() writes the HTML table-data tag () and the table-data closing tag (). In-between these tags, you see a call to pg_fetch_result() that retrieves a single value from the result set. When you call pg_fetch_result(), you provide three parameters: a result set handle, a row number, and a column number. pg_fetch_result() returns NULL if the requested value is NULL[3]. If not NULL, pg_fetch_result() will return the requested value in the form of a string. Note that the PHP/PostgreSQL documentation states numeric values are returned as float or integer values. This appears not to be the case; all values are returned in string form.

[3] In PHP 4.0 and above, NULL is equal to FALSE, but not identical to FALSE. This means that NULL == FALSE evaluates to trUE, but NULL === FALSE does not.

Now if you load client3a.php in your web browser, you'll see a table similar to that shown in Figure 15.6.

Figure 15.6. client3a.php loaded into your web browser.

 

Other Ways to Retrieve Result Set Values

Besides pg_fetch_result(), PHP provides a number of functions that retrieve result set values.

The pg_fetch_row() function returns an array of values that correspond to a given row. pg_fetch_row() requires two parameters: a result resource (also known as a result set handle) and a row number.

pg_fetch_row( resource result, int row_number )

 

Listing 15.12 shows the my_table.append_row() member function implemented in terms of pg_fetch_row().

Listing 15.12. append_row() Using pg_fetch_row()

...
 1 function append_row( $result, $row )
 2 {
 3 echo( "
" );
 4
 5 $values = pg_fetch_row( $result, $row );
 6
 7 for( $col = 0; $col < count( $values ); $col++ )
 8 {
 9 echo " ";
10 echo $values[$col];
11 echo "
";
12 }
13 echo( "
" );
14 }
...

 

In this version, you fetch the requested row at line 5. When the call to pg_fetch_row() completes, $values will contain an array of column values. You can access each array element using an integer index, starting at element 0.

The next function, pg_fetch_array(), is similar to pg_fetch_row(). Like pg_fetch_row(), pg_fetch_array() returns an array of columns values. The difference between these functions is that pg_fetch_array() can return a normal array (indexed by column number), an associative array (indexed by column name), or both. pg_fetch_array() expects one, two, or three parameters:

pg_fetch_array( resource result [, int row [, int result_type ]] )

 

The third parameter can be PGSQL_NUM, PGSQL_ASSOC, or PGSQL_BOTH. When you specify PGSQL_NUM, pg_fetch_array() operates identically to pg_fetch_row(); the return value is an array indexed by column number. When you specify PGSQL_ASSOC, pg_fetch_array() returns an associative array indexed by column name. If you specify PGSQL_BOTH, you will get back an array that can be indexed by column number as well as by column name. Listing 15.13 shows the append_row() function rewritten to use pg_fetch_array().

Listing 15.13. append_row() Using pg_fetch_array()

...
 1 function append_row( $result, $row )
 2 {
 3 echo( "
" );
 4
 5 $values = pg_fetch_array( $result, $row, PGSQL_ASSOC );
 6
 7 foreach( $values as $column_value )
 8 {
 9 echo " ";
10 echo $column_value;
11 echo "
";
12 }
13
14 echo( "
" );
15 }
...

 

You should note that this version of append_row() misses the point of using PGSQL_ASSOC. It ignores the fact that pg_fetch_array() has returned an associative array. Associative arrays make it easy to work with a result set if you know the column names ahead of time (that is, at the time you write your script), but they really don't offer much of an advantage for ad hoc queries. To really take advantage of pg_fetch_array(), you would write code such as

...
 $result = pg_query( $dbhandle, "SELECT * FROM customers;" );

 for( $row = 0; $row < pg_num_rows( $result ); $row++ )
 {
 $customer = pg_fetch_array( $result, $row, PGSQL_ASSOC );

 do_something_useful( $customer["customer_name"] );

 do_something_else( $customer["id"], $customer["phone"] );
 }
...

 

You can also obtain an associative array by calling pg_fetch_assoc( resource result [, int row] ). Calling pg_fetch_assoc() is equivalent to calling pg_fetch_array( ..., PGSQL_ASSOC ).

Another function useful for static queries is pg_fetch_object(). pg_fetch_object() returns a single row in the form of an object. The object returned has one field for each column, and the name of each field will be the same as the name of the column. For example:

...
 $result = pg_query( $dbhandle, "SELECT * FROM customers;" );

 for( $row = 0; $row < pg_num_rows( $result ); $row++ )
 {
 $customer = pg_fetch_object( $result, $row, PGSQL_ASSOC );

 do_something_useful( $customer->customer_name );

 do_something_else( $customer->id, $customer->phone );
 }
...

 

There is no significant difference between an object returned by pg_fetch_object() and an associative array returned by pg_fetch_array(). With pg_fetch_array(), you reference a value using $array[$column] syntax. With pg_fetch_object(), you reference a value using $object->$column syntax. Choose whichever syntax you prefer.

One warning about pg_fetch_object() and pg_fetch_array( ..., PGSQL_ASSOC)if your query returns two or more columns with the same column name, you will lose all but one of the columns. You can't have an associative array with duplicate index names, and you can't have an object with duplicate field names.

Metadata Access

You've seen that pg_fetch_object() and pg_fetch_array() expose column names to you, but the PHP/PostgreSQL API lets you get at much more metadata than just the column names.

The PHP/PostgreSQL interface is written using libpq (PostgreSQL's C-language API). Most of the functions available through libpq can be called from PHP, including the libpq metadata functions. Unfortunately, this means that PHP shares the limitations that you find in libpq.

In particular, the pg_field_size() function returns the size of a field. pg_field_size() expects two parameters:

int pg_field_size( resource $result, int $column_number )

 

The problem with this function is that the size reported is the number of bytes required to store the value on the server. It has nothing to do with the number of bytes seen by the client (that is, the number of bytes seen by your PHP script). For variable-length data types, pg_field_size() will return -1. If you're using a newer version of PHP (at least version 4.2.0) you can call pg_field_prtlen() to find the string length of a given value. You can call pg_field_prtlen() in either of the following forms:

int pg_field_prtlen( resource $result, int $row_number, int $column_number )
int pg_field_prtlen( resource $result, int $row_number, string $column_name )

 

The pg_field_type() function returns the name of the data type for a given column. pg_field_type() requires two parameters:

int pg_field_type( resource $result, int $column_number )

 

The problem with pg_field_type() is that it is not 100% accurate. pg_field_type() knows nothing of user-defined types or domains. Also, pg_field_type() won't return details about parameterized data types. For example, a column defined as NUMERIC( 7,2 ) is reported as type NUMERIC. Note: pg_field_type() has been improved in PHP version 5; it now queries the server to retrieve the name of the column's data type so it will return the correct name for user-defined types and domains (but it still doesn't return details about parameterized types).

Having conveyed the bad news, let's look at the metadata functions that are a little more useful for most applications.

You've already seen pg_num_rows() and pg_num_fields(). These functions return the number of rows and columns (respectively) in a result set.

The pg_field_name() and pg_field_num() functions are somewhat related. pg_field_name() returns the name of a column, given a column number index. pg_field_num() returns the column number index of a field given the field's name.

Let's enhance the my_table class a bit by including column names in the HTML table that we produce. Listing 15.14 shows a new version of the start_table() member function.

Listing 15.14. my_table.start_table()

 1 function start_table()
 2 {
 3 echo '';
 4
 5 for( $col = 0; $col < $this->columns; $col++ )
 6 {
 7 echo " 
";
10 }
11 echo "
";
12 }

I used the

cell. For some reason, web browsers draw an empty cell differently.

To fix this problem, you can modify append_row() to detect NULL values (see Listing 15.15).

Listing 15.15. my_table.append_row()

 1 function append_row( $result, $row )
 2 {
 3 echo( "

" ); 4 5 for( $col = 0; $col < $this->columns; $col++ ) 6 { 7 echo " "; 16 } 17 18 echo( " " ); 19 }

At line 9, you detect NULL values using the pg_field_is_null() function. If you encounter a NULL, you echo a non-breaking space character ( ) instead of an empty string. You have the same problem (a badly drawn border) if you encounter an empty string, and you fix it the same way (lines 11 and 12). Now, when you display a table, all the cells are drawn correctly, as shown in Figure 15.8.

Figure 15.8. client3a.phpfinal version.

 

There are a few more metadata functions that you can use in PHP, and you will need those functions in the next client that you write.

"; 8 echo pg_field_name( $this->result, $col ); 9 echo "	tag here instead of	, so that the browser knows that these are table header cells (table header cells are typically bolded and centered). Now when you browse to client3a.php, you see a nice set of column headers as shown in Figure 15.7. Figure 15.7. client3a.phpwith column headers.   Let's fix one other problem as long as we are fiddling with metadata. You may have noticed that the last row in Figure 15.7 looks a little funkythe phone number cell has not been drawn the same as the other cells. That happens when we try to create a table cell for a NULL value. If you look at the code that you built for the HTML table, you'll see that the last row has an empty
"; 8 9 if( pg_field_is_null( $result, $row, $col ) == 1 ) 10 echo " "; 11 elseif( strlen( pg_result( $result, $row, $col )) == 0 ) 12 echo " " 13 else 14 echo pg_result( $result, $row, $col ); 15 echo "

PHP, PostgreSQL, and Associative Functions

One of the more interesting abstractions promised (but not yet offered) by PHP and the PHP/PostgreSQL API is the associative function. An associative function gives you a way to execute a SQL command without having to construct the entire command yourself. Let's say that you need to INSERT a new row into the customers table. The most obvious way to do this in PHP is to build up an INSERT command by concatenating the new values and then executing the command using pg_query(). Another option is to use the pg_insert() function. With pg_insert(), you build an associative array. Each element in the array corresponds to a column. The key for a given element is the name of the column, and the value for the element is the value that you want to insert. For example, you can add a new row to the customers table with the following code:

...
$customer["id"] = 8;
$customer["customer_name"] = "Smallberries, John";
$customer["birth_date"] = "1985-05-14";

pg_insert( $db_handle, "customers", $customer );
...

 

In this code snippet, you have created an associative array with three entries. When you execute the call to pg_insert(), PHP will construct the following INSERT command:

INSERT INTO customers
 (
 id, 
 customer_name, 
 birth_date 
 )
 VALUES 
 (
 8, 
 'Smallberries, John',
 '1985-05-14'
 );

 

PHP knows the name of the table by looking at the second argument to pg_insert(). The column names are derived from the keys in the $customers array, and the values come from the values in the associative array.

Besides pg_insert(), you can call pg_delete() to build and execute a DELETE command. When you call pg_delete(), you provide a database handle, a table name, and an associative array. The associative array is used to construct a WHERE clause for the DELETE command. The values in the associative array are ANDed together to form the WHERE clause.

You can also use pg_select() to construct and execute a SELECT * command. pg_select() is similar to pg_delete()it expects a database handle, a table name, and an associative array. Like pg_delete(), the values in the associative array are ANDed together to form a WHERE clause.

Finally, the pg_update() function expects two associative arrays. The first array is used to form a WHERE clause, and the second array should contain the data (column names and values) to be updated.

As of PHP version 5.0, the associative functions are documented as experimental and are likely to change. Watch for these functions in a future release.