This section describes methods of the Adapter class with which you can run SELECT queries and retrieve the query results.
You can run a SQL SELECT query and retrieve
its results in one step using the fetchAll() method.
The first argument to this method is a string containing a SELECT statement. Alternatively, the first argument can be an object of class Zend_Db_Select. The Adapter automatically converts this object to a string representation of the SELECT statement.
The second argument to fetchAll() is an array of
values to substitute for parameter placeholders in the SQL
statement.
Example 189. Using fetchAll()
<?php
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $db->fetchAll($sql, 2);
By default, fetchAll() returns an array of
rows, each of which is an associative array. The keys of the
associative array are the columns or column aliases named in
the select query.
You can specify a different style of fetching results using the
setFetchMode() method. The modes supported are
identified by constants:
-
Zend_Db::FETCH_ASSOC: return data in an array of associative arrays. The array keys are column names, as strings. This is the default fetch mode forZend_Db_Adapterclasses.Note that if your select-list contains more than one column with the same name, for example if they are from two different tables in a JOIN, there can be only one entry in the associative array for a given name. If you use the
FETCH_ASSOCmode, you should specify column aliases in your SELECT query to ensure that the names result in unique array keys.By default, these strings are returned as they are returned by the database driver. This is typically the spelling of the column in the RDBMS server. You can specify the case for these strings, using the
Zend_Db::CASE_FOLDINGoption. Specify this when instantiating the Adapter. See this example Zend_Db::FETCH_NUM: return data in an array of arrays. The arrays are indexed by integers, corresponding to the position of the respective field in the select-list of the query.Zend_Db::FETCH_BOTH: return data in an array of arrays. The array keys are both strings as used in theFETCH_ASSOCmode, and integers as used in theFETCH_NUMmode. Note that the number of elements in the array is double that which would be in the array if you used eitherFETCH_ASSOCorFETCH_NUM.Zend_Db::FETCH_COLUMN: return data in an array of values. The value in each array is the value returned by one column of the result set. By default, this is the first column, indexed by 0.Zend_Db::FETCH_OBJ: return data in an array of objects. The default class is the PHP built-in class stdClass. Columns of the result set are available as public properties of the object.
Example 190. Using setFetchMode()
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchAll('SELECT * FROM bugs WHERE bug_id = ?', 2);
// $result is an array of objects
echo $result[0]->bug_description;
The fetchAssoc() method returns data in an array
of associative arrays, regardless of what value you have set
for the fetch mode, using the first column as the array index.
Example 191. Using fetchAssoc()
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchAssoc(
'SELECT bug_id, bug_description, bug_status FROM bugs'
);
// $result is an array of associative arrays, in spite of the fetch mode
echo $result[2]['bug_description']; // Description of Bug #2
echo $result[1]['bug_description']; // Description of Bug #1
The fetchCol() method returns data in an array
of values, regardless of the value you have set for the fetch mode.
This only returns the first column returned by the query.
Any other columns returned by the query are discarded.
If you need to return a column other than the first, see
this section.
Example 192. Using fetchCol()
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchCol(
'SELECT bug_description, bug_id FROM bugs WHERE bug_id = ?', 2);
// contains bug_description; bug_id is not returned
echo $result[0];
The fetchPairs() method returns data in an array
of key-value pairs, as an associative array with a single entry
per row. The key of this associative array is taken from the
first column returned by the SELECT query. The value is taken
from the second column returned by the SELECT query. Any other
columns returned by the query are discarded.
You should design the SELECT query so that the first column returned has unique values. If there are duplicates values in the first column, entries in the associative array will be overwritten.
Example 193. Using fetchPairs()
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchPairs('SELECT bug_id, bug_status FROM bugs');
echo $result[2];
The fetchRow() method returns data using the
current fetch mode, but it returns only the first row
fetched from the result set.
Example 194. Using fetchRow()
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchRow('SELECT * FROM bugs WHERE bug_id = 2');
// note that $result is a single object, not an array of objects
echo $result->bug_description;
The fetchOne() method is like a combination
of fetchRow() with fetchCol(),
in that it returns data only for the first row fetched from
the result set, and it returns only the value of the first
column in that row. Therefore it returns only a single
scalar value, not an array or an object.
Example 195. Using fetchOne()
<?php
$result = $db->fetchOne('SELECT bug_status FROM bugs WHERE bug_id = 2');
// this is a single string value
echo $result;