PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Reading Query Results

This section describes methods of the Adapter class with which you can run SELECT queries and retrieve the query results.

Fetching a Complete Result Set

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 195. Using fetchAll()

<?php
$sql 
'SELECT * FROM bugs WHERE bug_id = ?';

$result $db->fetchAll($sql2);

Changing the Fetch Mode

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 for Zend_Db_Adapter classes.

    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_ASSOC mode, 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_FOLDING option. 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 the FETCH_ASSOC mode, and integers as used in the FETCH_NUM mode. Note that the number of elements in the array is double that which would be in the array if you used either FETCH_ASSOC or FETCH_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 196. 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;

Fetching a Result Set as an Associative Array

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 197. 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

Fetching a Single Column from a Result Set

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 198. 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];

Fetching Key-Value Pairs from a Result Set

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 199. Using fetchPairs()

<?php
$db
->setFetchMode(Zend_Db::FETCH_OBJ);

$result $db->fetchPairs('SELECT bug_id, bug_status FROM bugs');

echo 
$result[2];

Fetching a Single Row from a Result Set

The fetchRow() method returns data using the current fetch mode, but it returns only the first row fetched from the result set.

Example 200. 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;

Fetching a Single Scalar from a Result Set

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 201. Using fetchOne()

<?php
$result 
$db->fetchOne('SELECT bug_status FROM bugs WHERE bug_id = 2');

// this is a single string value
echo $result;

Zend Framework