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

PEAR

PEAR (The PHP Extension and Application Repository) is an attempt to replicate the functionality of Perl's CPAN in the PHP community. To quote the official PEAR goals:

• To provide a consistent means for library code authors to share their code with other developers
• To give the PHP community an infrastructure for sharing code
• To define standards that help developers write portable and reusable code
• To provide tools for code maintenance and distribution

PEAR is primarily a large collection of PHP classes, which make use of PHP's object-oriented programming capabilities. You will therefore, need to become familiar with PHP's syntax for working with classes. PHP's object-oriented extensions are documented here: http://www.php.net/manual/en/language.oop.php.

More information on PEAR is available at:

• http://pear.php.net
• http://php.weblogs.com/php_pear_tutorials/

PEAR's Database Abstraction Interface

PEAR includes a database (DB) abstraction interface, which is included with the standard PHP distribution. The advantage to using a database abstraction interface instead of calling the database's native functions directly is code independence. Should you need to move your project to a different database, it would probably involve a major code rewrite. If you had used a database abstraction interface, however, the task would be trivial.

PEAR's DB interface also adds some value-added features, such as convenient access to multiple resultsets and integrated error handling. All of the database interaction is handled through the DB classes and objects. This is conceptually similar to Perl's DBI interface.

The main disadvantage to a database abstraction interface is the performance overhead it incurs on your applications execution. Once again, this is a situation where there is a trade-off between code flexibility and performance.

Using the DB Interface

The following example illustrates the use of the DB interface: note that this example assumes that the PEAR DB interface has already been installed and that it can be found via the current include_path setting. Both of these are the default for newer PHP4 installations:

<?php          
         
/* Import the PEAR DB interface. */          
require_once "DB.php";          
         
/* Database connection parameters. */          
$username = "jon";          
$password = "secret";          
$hostname = "localhost";          
$dbname = "bpsimple";          
         
/* Construct the DSN -- Data Source Name. */          
$dsn = "pgsql://$username:$password@$hostname/$dbname";          
         
/* Attempt to connect to the database. */          
$db = DB::connect($dsn);          
         
/* Check for any connection errors. */          
if (DB::isError($db)) {          
   die ($db->getMessage());          
}          
         
/* Execute a selection query. */          
$query = "SELECT title, fname, lname FROM customer";          
$result = $db->query($query);          
         
/* Check for any query execution errors. */          
if (DB::isError($result)) {          
   die ($result->getMessage());          
}          
         
/* Fetch and display the query results. */          
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {          
   $fullname = $row['title'] . " ";          
   $fullname .= $row['fname'] . " ";          
   $fullname .= $row['lname'];          
   echo "Customer: $fullname<br>\n";          
}          
         
/* Disconnect from the database. */          
$db->disconnect();          
         
?>

As you can see, this code, while not using any PostgreSQL functions directly, still follows the same programmatic logic of our previous examples. It is also easy to see how the above example could easily be adapted to use another type of database (Oracle or MySQL, for example) without much effort.

PEAR's Error Handling

Using the PEAR DB interface offers you as a developer a number of additional advantages. For example, PEAR includes an integrated error handling system. Here is some code to demonstrate error handling:

<?php          
         
/* Import the PEAR DB interface. */          
require_once 'DB.php';          
         
/* Construct the DSN -- Data Source Name. */          
$dsn = "pgsql://jon:secret@localhost/bpsimple";          
         
/* Attempt to connect to the database. */          
$db = DB::connect($dsn);          
         
/* Check for any connection errors. */          
if (DB::isError($db)) {          
   die ($db->getMessage());          
}

Above, we see the first instance of PEAR's error handling capabilities: DB::isError(). If the call to DB::connect() fails for some reason, it will return an PEAR_Error instance, instead of a database connection object. We can test for this case using the DB::isError() function, as shown above.

Knowing an error occurred is important, but finding out why that error occurred is even more important. We can retrieve the text of the error message (in this case, the connection error generated by PostgreSQL) using the getMessage() method of the PEAR_Error object. This is also demonstrated in the example above.

Our example continues with some queries:

/* Make errors fatal. */          
$db->setErrorHandling(PEAR_ERROR_DIE);          
         
/* Build and execute the query. */          
$query = "SELECT title, fname, lname FROM customer";          
$result = $db->query($query);          
         
/* Check for any query execution errors. */          
if (DB::isError($result)) {          
   die ($result->getMessage());          
}          
         
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {          
   $fullname = $row['title'] . " ";          
   $fullname .= $row['fname'] . " ";          
   $fullname .= $row['lname'];          
   echo "Customer: $fullname<br>\n";          
}          
         
/* Disconnect from the database. */          
$db->disconnect();          
         
?>

Note that we have changed PEAR's error handling behavior with the call to the setErrorHandling() method. Setting the error handling behavior to PEAR_ERROR_DIE will cause PHP to exit fatally if an error occurs.

Here's a list of the other error handling behaviors:

• PEAR_ERROR_RETURN Simply return an error object (default)
• PEAR_ERROR_PRINT Print the error message and continue execution
• PEAR_ERROR_TRIGGER Use PHP's trigger_error() function to raise an internal error
• PEAR_ERROR_DIE Print the error message and abort execution
• PEAR_ERROR_CALLBACK Use a callback function to handle the error before aborting execution

Additional information on the PEAR_Error class and PEAR error handling is available here: http://php.net/manual/en/class.pear-error.php.

Query Preparation and Execution

PEAR also includes a handle method of preparing and executing queries. Here's an abbreviated example demonstrating the prepare() and execute() methods of the DB interface. This example assumes we already have a valid database connection (from a DB::connect()):

/* Set up the $items array. */          
$items = array(          
   '6241527836190' => 20,          
   '7241427238373' => 21,          
   '7093454306788' => 22          
);          
         
/* Prepare our template SQL statement. */          
$statement = $db->prepare("INSERT INTO barcode VALUES(?,?)");          
         
/* Execute the statement for each entry in the $items array. */          
while (list($barcode, $item_id) = each($items)) {          
   $db->execute($statement, array($barcode, $item_id));          
}

This example probably requires some explanation for those of you who are unfamiliar with prepared SQL statements.

The call to the prepare() method creates a SQL template that can be executed repetitively. Note the two wildcard spots in the statement that are specified using question marks. These placeholders will be replaced with actual values later on when we call the execute() method.

Assuming we have an array of $items that contain barcodes and item IDs, we will want to perform one database insertion per item. To accomplish this, we construct a loop to iterate over each entry in the $items array, extract the barcode and item ID, and then execute the prepared SQL statement.

As mentioned above, the execute() method will replace the placeholder values in the prepared statement with those values passed to it in the second argument in array form. In the above example, this would be the array($barcode, $item_id) argument. The placeholder values are replaced in the order these new values are specified, so it's important to get them right.

Hopefully, you'll find this feature of the PEAR DB interface very useful in your own projects.