PHP : Database Access - [10.6] Retrieving Rows Without a Loop

10.6.1 Problem

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

10.6.2 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'");

10.6.3 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 an array of results whose values are usually strings. The results from getAssoc( ) are returned as an array. The type of elements of that array are controlled by the fetch mode.

Like DB::query( ), you can pass these functions a query with placeholders in it and an array of parameters to fill the placeholders. The parameters are properly quoted when they replace the placeholders in the query:

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

The parameter array is the second argument to each of these functions, except getCol( ) and getAssoc( ). For these two functions, the parameter array is the third argument. The second argument to getCol( ) is a column number to return if you don't want the first column (column number 0). For example, this returns the values of the planet column:

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

The second argument to getAssoc( ) is a boolean that tells the function whether to force the values in the associative array it returns to be arrays themselves even if they could be scalars. Take this query for example:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol FROM zodiac WHERE element LIKE 'water'");
print_r($assoc);
Array
(
    [Cancer] => Crab
    [Scorpio] => Scorpion
    [Pisces] => Fishes
)

Because the query passed to getAssoc( ) asks only for two columns, the first column is the array key, and the second column is the scalar array value. Here's how to force the array values to be one-element arrays:

$assoc = $dbh->getAssoc(
    "SELECT sign,symbol FROM zodiac WHERE element LIKE 'water'",true);
print_r($assoc);
Array
(
    [Cancer] => Array
        (
            [0] => Crab
        )
    [Scorpio] => Array
        (
            [0] => Scorpion
        )
    [Pisces] => Array
        (
            [0] => Fishes
        )
)

Just as fetchRow( ) and fetchInto( ) do, getRow( ), getAssoc( ), and getAll( ) put data in numeric arrays by default. You can pass them a fetch mode (the third argument to getRow( ) or getAll( ), the fourth argument to getAssoc( )). They also respect the fetch mode set by DB::setFetchMode( ).