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

Fetching Results from a SELECT Statement

You can call methods on the statement object to retrieve rows from SQL statements that produce result set. SELECT, SHOW, DESCRIBE and EXPLAIN are examples of statements that produce a result set. INSERT, UPDATE, and DELETE are examples of statements that don't produce a result set. You can execute the latter SQL statements using Zend_Db_Statement, but you cannot call methods to fetch rows of results from them.

Fetching a Single Row from a Result Set

To retrieve one row from the result set, use the fetch() method of the statement object. All three arguments of this method are optional:

  • Fetch style is the first argument. This controls the structure in which the row is returned. See this chapter for a description of the valid values and the corresponding data formats.

  • Cursor orientation is the second argument. The default is Zend_Db::FETCH_ORI_NEXT, which simply means that each call to fetch() returns the next row in the result set, in the order returned by the RDBMS.

  • Offset is the third argument. If the cursor orientation is Zend_Db::FETCH_ORI_ABS, then the offset number is the ordinal number of the row to return. If the cursor orientation is Zend_Db::FETCH_ORI_REL, then the offset number is relative to the cursor position before fetch() was called.

fetch() returns FALSE if all rows of the result set have been fetched.

Example 225. Using fetch() in a loop

<?php
$stmt 
$db->query('SELECT * FROM bugs');

while (
$row $stmt->fetch()) {
    echo 
$row['bug_description'];
}

See also PDOStatement::fetch().

Fetching a Complete Result Set

To retrieve all the rows of the result set in one step, use the fetchAll() method. This is equivalent to calling the fetch() method in a loop and returning all the rows in an array. The fetchAll() method accepts two arguments. The first is the fetch style, as described above, and the second indicates the number of the column to return, when the fetch style is Zend_Db::FETCH_COLUMN.

Example 226. Using fetchAll()

<?php
$stmt 
$db->query('SELECT * FROM bugs');

$rows $stmt->fetchAll();

echo 
$rows[0]['bug_description'];

See also PDOStatement::fetchAll().

Changing the Fetch Mode

By default, the statement object returns rows of the result set as associative arrays, mapping column names to column values. You can specify a different format for the statement class to return rows, just as you can in the Adapter class. You can use the setFetchMode() method of the statement object to specify the fetch mode. Specify the fetch mode using Zend_Db class constants FETCH_ASSOC, FETCH_NUM, FETCH_BOTH, FETCH_COLUMN, and FETCH_OBJ. See this chapter for more information on these modes. Subsequent calls to the statement methods fetch() or fetchAll() use the fetch mode that you specify.

Example 227. Setting the fetch mode

<?php
$stmt 
$db->query('SELECT * FROM bugs');

$stmt->setFetchMode(Zend_Db::FETCH_NUM);

$rows $stmt->fetchAll();

echo 
$rows[0][0];

See also PDOStatement::setFetchMode().

Fetching a Single Column from a Result Set

To return a single column from the next row of the result set, use fetchColumn(). The optional argument is the integer index of the column, and it defaults to 0. This method returns a scalar value, or FALSE if all rows of the result set have been fetched.

Note this method operates differently than the fetchCol() method of the Adapter class. The fetchColumn() method of a statement returns a single value from one row. The fetchCol() method of an adapter returns an array of values, taken from the first column of all rows of the result set.

Example 228. Using fetchColumn()

<?php
$stmt 
$db->query('SELECT bug_id, bug_description, bug_status FROM bugs');

$bug_status $stmt->fetchColumn(2);

See also PDOStatement::fetchColumn().

Fetching a Row as an Object

To retrieve a row from the result set structured as an object, use the fetchObject(). This method takes two optional arguments. The first argument is a string that names the class name of the object to return; the default is 'stdClass'. The second argument is an array of values that will be passed to the constructor of that class.

Example 229. Using fetchObject()

<?php
$stmt 
$db->query('SELECT bug_id, bug_description, bug_status FROM bugs');

$obj $stmt->fetchObject();

echo 
$obj->bug_description;

See also PDOStatement::fetchObject().

Zend Framework