ïÐÉÓÁÎÉÅ
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.
Пример 22-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 (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()) {
// Assuming DB's default fetchmode is
// DB_FETCHMODE_ORDERED
echo $row[0] . "\n";
}
// Or:
// an example using fetchInto()
while ($res->fetchInto($row)) {
// 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
) |
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.
Пример 22-2. Determining fetch mode per call
<?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";
}
?>
|
|
Пример 22-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 ($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.
Пример 22-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 (!$row =& $res->fetchRow($fetchmode, $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().
Пример 22-5. Freeing
<?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();
?>
|
|
Getting More Information From Query Results
With DB there are four ways to retrieve useful
information about the query result sets themselves:
Checking for Errors
Don't forget to use
isError() to check
if your actions return a DB_Error object.