PEAR Manual
PrevNext

Introduction - Results

Introduction - Results -- Obtaining data from query results

Description

Fetching Individual Rows From Query Results

The DB_result object provides two methods for fetching data from rows of a result set: fetchRow() and fetchInto().

fetchRow() returns the row's data. fetchInto() assigns the row's data to a variable you provide and returns DB_OK.

The result pointer gets moved to the next row each time these methods are called. NULL is returned when the end of the result set is reached.

DB_Error is returned if an error is encountered.

Example 33-1. Fetching a result set

<?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 (PEAR::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 ($res->fetchInto($row)) {
    // Assuming DB's default fetchmode is DB_FETCHMODE_ORDERED
    echo $row[0] . "\n";
}

// Or, you could have done the same thing using fetchRow()
// while ($row =& $res->fetchRow()) {
//     // Assuming DB's default fetchmode is DB_FETCHMODE_ORDERED
//     echo $row[0] . "\n";
// }
?>

Formats of Fetched Rows

The data from the row of a query result can be placed into one of three constructs: an ordered array (with column numbers as keys), an associative array (with column names as keys) or an object (with column names as properties).

DB_FETCHMODE_ORDERED (default) 
Array
(
    [0] => 28
    [1] => hi
)

DB_FETCHMODE_ASSOC 
Array
(
    [a] => 28
    [b] => hi
)

DB_FETCHMODE_OBJECT 
stdClass Object
(
    [a] => 28
    [b] => hi
)

NOTE: When a query contains the same column name more than once (such as when joining tables which have duplicate column names) and the fetch mode is DB_FETCHMODE_ASSOC or DB_FETCHMODE_OBJECT, the data from the last column with a given name will be the one returned. There are two immediate options to work around this issue:

Use aliases in your query, for example People.Name AS PersonName
Change the fetch mode to DB_FETCHMODE_ORDERED

TIP: If you are running into this issue, it likely indicates poor planning of the database schema. Either data is needlessly being duplicated or the same names are being used for different kinds of data.

How to Set Formats

You can set the fetch mode each time you call a fetch method and/or you can set the default fetch mode for the whole DB instance by using the setFetchMode() method.

Example 33-2. Determining fetch mode per call

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

while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
    echo $row['id'] . "\n";
}
?>

Example 33-3. Changing default fetch mode

<?php
// Once you have a valid DB object named $db...
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$res =& $db->query('SELECT * FROM users');

while ($res->fetchInto($row)) {
    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.

Example 33-4. Fetching by number

<?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 (!$res->fetchInto($row, DB_FETCHMODE_ORDERED, $rowNum)) {
        break;
    }
    echo $row[0] . "\n";
}
?>

Getting Entire Result Sets

The DB_common object provides several methods that make data retrieval easy by combining the processes of running of the query string you provide, putting the returned information into a PHP data structure and freeing the results. These methods include getOne(), getRow(), getCol(), getAssoc() and getAll().

Freeing Result Sets

Once you finish using a result set, if your script continues for a while, it's a good idea to save memory by freeing the result set via Use free().

Example 33-5. Freeing

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

Getting More Information From Query Results

With DB there are four ways to retrieve useful information about the query result sets themselves:

Example 33-6. numRows() tells how many rows are in a SELECT query result 

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

Example 33-7. numCols() tells how many columns are in a SELECT query result 

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

Example 33-8. affectedRows() tells how many rows were altered by a data change 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';
?>

Example 33-9. tableInfo() returns an associative array with information about the columns in a SELECT query result 

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

// That usage works for DB 1.6.0 or later.
// Below is the syntax for earlier versions:
print_r($res->tableInfo());
?>

Checking for Errors

Don't forget to use isError() to check if your actions return a DB_Error object.

PrevHomeNext
Introduction - QueryUpIntroduction - Prepare & Execute