Introduction - Results

Introduction - Results -- Obtaining data from query results

Описание

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.

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.

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().

Getting More Information From Query Results

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

Пример 35-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();
?>

Пример 35-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();
?>

Пример 35-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';
?>

Пример 35-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.

    Поддержать сайт на родительском проекте КГБ