Pear::DB Primer
Pages: 1, 2

Selecting something from the database

Now that we can connect to a database server using PEAR::DB, let's run a query and select some rows from it. As always, there are a set of standard methods to use in such cases as seen below:

<?php
$stmt = "SELECT
            id,
            first_name,
            last_name
         FROM
            persons
         LIMIT
            0, 3";
$rows = $dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
?>

The example above runs a query and returns the full result set as one nested array. The DB_FETCHMODE_ASSOC constant passed to the method tells PEAR::DB that I want the resulting array with a nested associative array, with the field name as the key. Here you see its structure:

array(
  0 => array(
          "id" => 12,
          "first_name" => "Rasmus",
          "last_name" => "Lerdorf"
       ),
  1 => array(
          "id" => 13,
          "first_name" => "Stig",
          "last_name" => "Bakken"
       ),
  2 => array(
          "id" => 14,
          "first_name" => "Joao",
          "last_name" => "Prado Maia"
       )
)

There are several alternatives to this method, such as the fetchRow() method, which does the same thing as the mysql_fetch_* family of functions. Similar methods like getOne() and getRow() are very useful for those cases where you are selecting just one value from the only row being returned, or even selecting the first full row of data being returned.

<?php
$stmt = "SELECT first_name FROM persons WHERE id=14";
$first_name = $dbh->getOne($stmt);
if (PEAR::isError($first_name)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $first_name->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $first_name->getDebugInfo() . "<br>\n";
}

$stmt = "SELECT id, first_name, last_name FROM persons WHERE id=14";
$row = $dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($row)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $row->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $row->getDebugInfo() . "<br>\n";
}
?>

As always, every returned value from PEAR::DB can eventually contain a PEAR_Error object, so checking for that is appropriate.

Updating, deleting, and inserting into the database

Now that I covered how to select information from a table using the PEAR::DB get* family of methods, let's look on how easy it is to update the contents of an existing row, or even delete or insert a new row.

<?php
$stmt = "INSERT INTO
            persons
         (
            first_name,
            last_name,
            birth_date
         ) VALUES (
            'Zeev',
            'Suraski',
            '0000-00-00 00:00:00'
         )";
$result = $dbh->query($stmt);
if (PEAR::isError($result)) {
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $result->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $result->getDebugInfo() . "<br>\n";
} else {
    echo "Thank you, the new row was inserted successfully.";
}
?>

Simple, isn't it? Queries like UPDATE and DELETE behave in exactly the same way. With these types of queries, the method to use is query(), and it will return a standard PEAR_Error instance on error situations or "true" for success.

Now you are probably thinking that constantly checking every PEAR_Error message is a bit excessive. A more automatic way to check for errors should surely exist, correct? Here's an example of how to automate error handling:

<?php
function handleErrors($error)
{
    echo "An error occurred while trying to run your query.<br>\n";
    echo "Error message: " . $error->getMessage() . "<br>\n";
    echo "A more detailed error description: " . $error->getDebugInfo() . "<br>\n";
}
include("DB.php");
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');
$dbh = DB::connect($dsn);
// the next line triggers the automatic error handling function
$rows = $dbh->getAll('SELECT unknown_field FROM persons');
?>

The example above runs the handleError() function automatically whenever an error happens inside the PEAR::DB code. This is quite useful and will save a few thousand keystrokes if you are developing a big project.

I hope this article gave a little more attention to the PEAR project. Detailed information on the current developments can be seen on the PEAR-DEV and PEAR-GENERAL mailing lists.

The PEAR project is also beginning to develop its own site, with lots of information about the libraries and a roadmap for the future, so please be sure to visit the site.

Joao Prado Maia is a web developer living in Houston with more than four years of experience developing web-based applications and loves learning new technologies and programming languages.

---

Return to the PHP DevCenter.