PHP Cookbook

10.5. Retrieving Rows Without a Loop

Problem

You want a concise way to execute a query and retrieve the data it returns.

Solution

With PEAR DB, use DB::getRow( ) to retrieve the first (or only) row from a query:

$row = $dbh->getRow("SELECT planet,symbol FROM zodiac WHERE sign LIKE 'Pisces'");

Use DB::getAll( ) to retrieve all rows from a query:

$rows = $dbh->getAll("SELECT planet,symbol FROM zodiac WHERE element LIKE 'fire'");

Use DB::getOne( ) to retrieve just one column from one row:

$col = $dbh->getOne("SELECT symbol FROM zodiac WHERE sign = 'Libra'");

Use DB::getCol( ) to retrieve a column from all rows:

$cols = $dbh->getCol('SELECT symbol FROM zodiac');

Use DB::getAssoc( ) to retrieve all rows from a query into an associative array indexed by the first column of the query:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol,planet FROM zodiac WHERE element LIKE 'water'");

Discussion

All these functions return a DB_Error object if an error occurs in executing a query or retrieving the results. If the query returns no results, getRow( ) and getOne( ) return NULL; getAll( ), getCol( ), and getAssoc( ) return an empty array.

When returning results, getRow( ) returns an array or object, depending on the current fetch mode. The getAll( ) method returns an array of arrays or array of objects, also depending on the fetch mode. The single result getOne( ) returns is usually a string, because PHP database drivers generally cast retrieved results into strings. Similarly, getCol( ) returns ...