Introduction - Fetch & Get

Overview of Fetch Functions

The DB_result object provides two functions to fetch rows: fetchRow() and fetchInto(). fetchRow() returns the row, NULL on no more data or a DB_Error, when an error occurs. fetchInto() requires a variable, which be will directly assigned by reference to the result row. It will return NULL when result set is empty or a DB_Error too.

<?php // Create a valid DB object named $db // at the beginning of your program... require_once 'DB.php';  $db =& DB::connect('pgsql://usr:pw@localhost/dbnam'); if (DB::isError($db)) {     die($db->getMessage()); }  // Proceed with getting some data... $res =& $db->query('SELECT * FROM mytable');  // Get each row of data on each iteration until // there are no more rows while ($row =& $res->fetchRow()) {     echo $row[0] . "\n"; }  // Or: // an example using fetchInto() while ($res->fetchInto($row)) {     echo $row[0] . "\n"; } ?>

Choosing Formats for Fetched Rows

The fetch modes supported are:

• DB_FETCHMODE_ORDERED (default)

  The fetch*() returns an ordered array. The order is taken from the select statement.

  Example 21-2. Fetch a ordered array <?php // Once you have a valid DB object named $db... $db->setFetchMode(DB_FETCHMODE_ORDERED); $res =& $db->query('INSERT INTO phptest ' . "(a, b) VALUES (28, 'hi')"); $res =& $db->query('SELECT * FROM phptest WHERE a = 28'); $row =& $res->fetchRow(); print_r($row); echo 'Column a is ' . $row[0]; /* * Output... * Array * ( * [0] => 28 * [1] => hi * ) * Column a is 28 */ ?>

• DB_FETCHMODE_ASSOC

  Returns an associative array with the column names as the array keys

  Example 21-3. Fetch a assoc. array <?php // Once you have a valid DB object named $db... $db->setFetchMode(DB_FETCHMODE_ASSOC); $res =& $db->query('INSERT INTO phptest ' . "(a, b) VALUES (28, 'hi')"); $res =& $db->query('SELECT * FROM phptest WHERE a = 28'); $row =& $res->fetchRow(); print_r($row); echo 'Column a is ' . $row['a']; /* * Output... * Array * ( * [a] => 28 * [b] => hi * ) * Column a is 28 */ ?>

• DB_FETCHMODE_OBJECT

  Returns an object with column names as properties

  If setFetchMode() is called without the optional second argument, fetchRow() returns a stdClass object.

  Example 21-4. Fetching into an object <?php // Once you have a valid DB object named $db... $db->setFetchMode(DB_FETCHMODE_OBJECT); $res =& $db->query('INSERT INTO phptest ' . "(a, b) VALUES (28, 'hi')"); $res =& $db->query('SELECT * FROM phptest WHERE a = 28'); $row =& $res->fetchRow(); print_r($row); echo 'Column a is ' . $row->a; /* * Output... * stdClass Object * ( * [a] => 28 * [b] => hi * ) * Column a is 28 */ ?>

  If setFetchMode()'s optional parameter is passed DB_row, fetchRow() returns a DB_row object.

  Example 21-5. Fetching into a DB_row object <?php // Once you have a valid DB object named $db... $db->setFetchMode(DB_FETCHMODE_OBJECT, 'DB_row'); $res =& $db->query('INSERT INTO phptest ' . "(a, b) VALUES (28, 'hi')"); $res =& $db->query('SELECT * FROM phptest WHERE a = 28'); $row =& $res->fetchRow(); print_r($row); echo 'Column a is ' . $row->a; /* * Output... * db_row Object * ( * [a] => 28 * [b] => hi * ) * Column a is 28 */ ?>

How to Set Formats

You can set the fetch mode per result call or for your whole DB instance.

<?php // Once you have a valid DB object named $db... $res =& $db->query('SELECT * FROM users');  while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {     echo $row['id'] . "\n"; } ?>

<?php // Once you have a valid DB object named $db... $db->setFetchMode(DB_FETCHMODE_ASSOC);  $res =& $db->query('SELECT * FROM users');  while ($row =& $res->fetchRow()) {     echo $row['id'] . "\n"; } ?>

Fetch Rows by Number

The PEAR DB fetch system also supports an extra parameter to the fetch statement. So you can fetch rows from a result by number. This is especially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.

<?php // Once you have a valid DB object named $db...  // the row to start fetching $from = 50;  // how many results per page $resPage = 10;  // the last row to fetch for this page $to = $from + $resPage;  foreach (range($from, $to) as $rowNum) {     if (!$row =& $res->fetchRow($fetchmode, $rowNum)) {         break;     }     echo $row[0] . "\n";     .... } ?>

Freeing Result Sets

It is recommended to finish the result set after processing in order to save memory. Use free() to do this.

<?php // Once you have a valid DB object named $db... $res =& $db->query('SELECT name, address FROM clients'); while ($row =& $res->fetchRow()) {     echo $row['name'] . ', ' . $row['address'] . "\n"; } $res->free(); ?>

Quick Data Retrieving

DB provides some special ways to retrieve information from a query without the need of using fetch*() and looping through results.

getOne() retrieves the first result of the first column from a query

<?php // Once you have a valid DB object named $db... $numrows = $db->getOne('select count(id) from clients'); ?>

getRow() returns the first row as an array.

<?php // Once you have a valid DB object named $db... $sql = 'select name, address from clients where id=1'; while ($row =& $db->getRow($sql)) {     echo $row['name'] . ', ' . $row['address'] . "\n"; } ?>

getCol() returns an array with the data of the selected column. It accepts the column number to retrieve as the second parameter.

<?php // Once you have a valid DB object named $db... $data =& $db->getCol('SELECT name FROM clients');  /*  * Will return:  * array(  *     0 => 'Stig',  *     1 => 'Jon',  *     2 => 'Colin'  * )  */ ?>

getAssoc() fetches the entire result set of a query and return it as an associative array using the first column as the key.

<?php // Once you have a valid DB object named $db... $data =& $db->getAssoc('SELECT name, surname, phone FROM mytable'); /*  * Will return:  * array(  *     'Peter'   => array('Smith', '944679408'),  *     'Tomas'   => array('Cox', '944679408'),  *     'Richard' => array('Merz', '944679408')  * )  */ ?>

getAll() fetches all the rows returned from a query-

<?php // Once you have a valid DB object named $db... $data =& $db->getAll('SELECT id, text, date FROM mytable'); /*  * Will return:  * array(  *     0 => array('4', 'four', '2004'),  *     1 => array('5', 'five', '2005'),  *     2 => array('6', 'six', '2006')  * )  */ ?>

The get*() family methods will do all the dirty job for you, this is: launch the query, fetch the data and free the result. Please note that as all PEAR DB functions they will return a DB_Error object on errors.

Getting More Information From Query Results

With DB you have many ways to retrieve useful information from query results. These are:

• numRows(): Returns the total number of rows returned from a SELECT query.

  <?php // Once you have a valid DB object named $db... $res =& $db->query('SELECT * FROM phptest'); echo $res->numRows(); ?>

• numCols(): Returns the total number of columns returned from a SELECT query.

  <?php // Once you have a valid DB object named $db... $res =& $db->query('SELECT * FROM phptest'); echo $res->numCols(); ?>

• affectedRows(): Returns the number of rows affected by a data manipulation query (INSERT, UPDATE or DELETE).

  <?php // remember that this statement won't return a result object $db->query('DELETE * FROM clients'); echo 'I have deleted ' . $db->affectedRows() . ' clients'; ?>

• tableInfo(): Returns an associative array with information about the returned fields from a SELECT query.

  <?php // Once you have a valid DB object named $db... $res =& $db->query('SELECT * FROM phptest'); print_r($db->tableInfo($res)); ?>