CHAPTER 6 – PEAR DB – Fetching Results

The DB_result class has two methods for fetching results and three ways of representing a row of data.

Fetch Modes As with most native database extensions, DB offers dif- ferent ways of representing a row of data: DB_FETCHMODE_ORDERED, returning a numerically indexed array, like this: array( 0 => first column, 1 => second column, 2 => third column, ... ) DB_FETCHMODE_ASSOC, returning an associative array with column names as keys: array( "ID" => first column, "Name" => second column, "CountryCode" => third column, ... ) DB_FETCHMODE_OBJECT, returning an object with public member variables named after column names. The default fetch mode is DB_FETCHMODE_ORDERED.

Configuring Fetch Modes You may change the default fetch mode by calling the setFetchMode() method in the connection object, like this: $dbh->setFetchMode(DB_FETCHMODE_ASSOC); This fetch mode then applies to any queries executed by this connection object. You may also override the default fetch mode per query with an extra parameter to the fetch methods, like this: $row = $result->fetchRow(DB_FETCHMODE_OBJECT); // or like this: $result->fetchInto($row, DB_FETCHMODE_ASSOC);  

fetchRow($fetchmode = DB_FETCHMODE_ORDERED, $row = 0) This method returns an array with row data. fetchRow() returns the array or object with row data on success, NULL when reaching the end of the result set, or a DB error object.  

fetchInto(&$arrr, $fetchmode = DB_FETCHMODE_ORDERED, $row = 0) fetchInto() returns DB_OK and stores the row data in $arr when a row was successfully retrieved, returns NULL when reaching the end of the result set, or returns a DB error object. As it happens, DB_OK evaluates to true and NULL evaluates to false. Provided you have an error handler set up, you can then write a loop, like this: while ($result->fetchInto($row)) { // ... do something } In general, it is always better to use fetchInto(). It makes looping over results easier and slightly faster because fetchRow() is really just a wrapper around fetchInto().  

Using Your Own Result Class By default, the object fetch mode (DB_FETCHMODE_OB JECT) returns a stdClass object. If you configure the fetch mode using the DB::setFetchMode() method rather than specifying the fetch mode in the fetch call, you can add an extra parameter to specify the class to use for the returned object. The only interface requirement is that the constructor must accept a sin- gle array parameter. The array passed to the constructor will have the row data indexed by column name. You can configure your own class only when controlling the fetch mode with DB::setFetchMode(). Here is an example that uses a class implementing a getter method to access row data: <?php require_once 'DB.php'; class MyResultClass { public $row_data; function __construct($data) { $this->row_data = $data; } function __get($variable) { return $this->row_data[$variable]; } } PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />n"); $dbh = DB::connect("mysql://test@localhost/world"); $dbh->setFetchMode(DB_FETCHMODE_OBJECT, "MyResultClass"); $code = 'NOR'; $result = $dbh->query("SELECT Name FROM City WHERE CountryCode = ?", $code); while ($row = $result->fetchRow()) { print $row->Name . "<br />n"; }