7.9 Changing the Format of Retrieved Rows

So far, fetchRow( ), getAll( ), and getOne( ) have been returning rows from the database as numerically indexed arrays. This makes for concise and easy interpolation of values in double-quoted strings—but trying to remember, for example, which column from the SELECT query corresponds to element 6 in the result array can be difficult and error-prone. PEAR DB lets you specify that you'd prefer to have each result row delivered as either an array with string keys or as an object.

The fetch mode controls how result rows are formatted. The setFetchMode( ) function changes the fetch mode. Any queries in a page after you call setFetchMode( ) have their result rows formatted as specified by the argument to setFetchMode( ).

To get result rows as arrays with string keys, pass DB_FETCHMODE_ASSOC to setFetchMode( ). Note that DB_FETCHMODE_ASSOC is a special constant defined by PEAR DB, not a string, so you shouldn't put quotes around it. The array keys in the result row arrays correspond to column names. Example 7-46 shows how to use fetchRow( ), getAll( ), and getRow( ) with string-keyed result rows.

Example 7-46. Retrieving rows as string-keyed arrays

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');

// Change the fetch mode to string-keyed arrays
$db->setFetchMode(DB_FETCHMODE_ASSOC);

print "With query( ) and fetchRow( ): \n";
// get each row with query( ) and fetchRow( );
$q = $db->query("SELECT dish_name, price FROM dishes");
while($row = $q->fetchRow( )) {
    print "The price of $row[dish_name] is $row[price] \n";
}

print "With getAll( ): \n";
// get all the rows with getAll( );
$dishes = $db->getAll('SELECT dish_name, price FROM dishes');
foreach ($dishes as $dish) {
    print "The price of $dish[dish_name] is $dish[price] \n";
}

print "With getRow( ): \n";
$cheap = $db->getRow('SELECT dish_name, price FROM dishes
    ORDER BY price LIMIT 1');
print "The cheapest dish is $cheap[dish_name] with price $cheap[price]";

Example 7-46 prints:

With query( ) and fetchRow( ):
The price of Walnut Bun is 1.00
The price of Cashew Nuts and White Mushrooms is 4.95
The price of Dried Mulberries is 3.00
The price of Eggplant with Chili Sauce is 6.50
With getAll( ):
The price of Walnut Bun is 1.00
The price of Cashew Nuts and White Mushrooms is 4.95
The price of Dried Mulberries is 3.00
The price of Eggplant with Chili Sauce is 6.50
With getRow( ):
The cheapest dish is Walnut Bun with price 1.00

In Example 7-46, fetchRow( ), getAll( ), and getRow( ) operate almost identically as they have before: you give them an SQL query, and you get back some results. The difference is in those results. The rows that come back from these functions have string keys whose names are the names of columns in the database table.

To get result rows as objects, pass the DB_FETCHMODE_OBJECT constant to setFetchMode( ). Each result row is an object with values inside it whose names correspond to column names (such as the string array keys when the fetch mode is DB_FETCHMODE_ASSOC). The DB_FETCHMODE_OBJECT fetch mode is handy because the syntax for referring to data inside an object is a little more concise and easier to interpolate in a string compared to an string-keyed array: write the object name, then ->, and then the name of the piece of data you want. For example, $dish->dish_name refers to the piece of data named dish_name inside the $dish object. Example 7-47 retrieves rows as objects.

Example 7-47. Retrieving rows as objects

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');

// Change the fetch mode to objects
$db->setFetchMode(DB_FETCHMODE_OBJECT);

print "With query( ) and fetchRow( ): \n";
// get each row with query( ) and fetchRow( );
$q = $db->query("SELECT dish_name, price FROM dishes");
while($row = $q->fetchRow( )) {
    print "The price of $row->dish_name is $row->price \n";
}

print "With getAll( ): \n";
// get all the rows with getAll( );
$dishes = $db->getAll('SELECT dish_name, price FROM dishes');
foreach ($dishes as $dish) {
    print "The price of $dish->dish_name is $dish->price \n";
}

print "With getRow( ): \n";
$cheap = $db->getRow('SELECT dish_name, price FROM dishes
    ORDER BY price LIMIT 1');
print "The cheapest dish is $cheap->dish_name with price $cheap->price";

Example 7-47 prints the same output as Example 7-46.