Querying for a Set of Rows

Select API

Warning

The API for fetch operations has been superseded to allow a Zend_Db_Table_Select object to modify the query. However, the deprecated usage of the fetchRow() and fetchAll() methods will continue to work without modification.

The following statements are all legal and functionally identical, however it is recommended to update your code to take advantage of the new usage where possible.


<?php
/**
 * Fetching a rowset
 */
$rows = $table->fetchAll(
    'bug_status = "NEW"',
    'bug_id ASC',
    10,
    0
    );
$rows = $table->fetchAll(
    $table->select()
        ->where('bug_status = ?', 'NEW')
        ->order('bug_id ASC')
        ->limit(10, 0)
    );
// or with binding
$rows = $table->fetchAll(
    $table->select()
        ->where('bug_status = :status')
        ->bind(array(':status'=>'NEW')
        ->order('bug_id ASC')
        ->limit(10, 0)
    );

/**
 * Fetching a single row
 */
$row = $table->fetchRow(
    'bug_status = "NEW"',
    'bug_id ASC'
    );
$row = $table->fetchRow(
    $table->select()
        ->where('bug_status = ?', 'NEW')
        ->order('bug_id ASC')
    );
// or with binding
$row = $table->fetchRow(
    $table->select()
        ->where('bug_status = :status')
        ->bind(array(':status'=>'NEW')
        ->order('bug_id ASC')
    );

The Zend_Db_Table_Select object is an extension of the Zend_Db_Select object that applies specific restrictions to a query. The enhancements and restrictions are:

You can elect to return a subset of columns within a fetchRow or fetchAll query. This can provide optimization benefits where returning a large set of results for all columns is not desirable.
You can specify columns that evaluate expressions from within the selected table. However this will mean that the returned row or rowset will be readOnly and cannot be used for save() operations. A Zend_Db_Table_Row with readOnly status will throw an exception if a save() operation is attempted.
You can allow JOIN clauses on a select to allow multi-table lookups.
You can not specify columns from a JOINed tabled to be returned in a row or rowset. Doing so will trigger a PHP error. This was done to ensure the integrity of the Zend_Db_Table is retained. i.e. A Zend_Db_Table_Row should only reference columns derived from its parent table.

Example 279. Simple usage


<?php
$table = new Bugs();

$select = $table->select();
$select->where('bug_status = ?', 'NEW');

$rows = $table->fetchAll($select);

Fluent interfaces are implemented across the component, so this can be rewritten this in a more abbreviated form.

Example 280. Example of fluent interface


<?php
$table = new Bugs();

$rows =
    $table->fetchAll($table->select()->where('bug_status = ?', 'NEW'));

Fetching a rowset

You can query for a set of rows using any criteria other than the primary key values, using the fetchAll() method of the Table class. This method returns an object of type Zend_Db_Table_Rowset_Abstract.

Example 281. Example of finding rows by an expression


<?php
$table = new Bugs();

$select = $table->select()->where('bug_status = ?', 'NEW');

$rows = $table->fetchAll($select);

You may also pass sorting criteria in an ORDER BY clause, as well as count and offset integer values, used to make the query return a specific subset of rows. These values are used in a LIMIT clause, or in equivalent logic for RDBMS brands that do not support the LIMIT syntax.

Example 282. Example of finding rows by an expression


<?php
$table = new Bugs();

$order  = 'bug_id';

// Return the 21st through 30th rows
$count  = 10;
$offset = 20;

$select = $table->select()->where('bug_status = ?', 'NEW')
                          ->order($order)
                          ->limit($count, $offset);

$rows = $table->fetchAll($select);

All of the arguments above are optional. If you omit the ORDER clause, the result set includes rows from the table in an unpredictable order. If no LIMIT clause is set, you retrieve every row in the table that matches the WHERE clause.

Advanced usage

For more specific and optimized requests, you may wish to limit the number of columns returned in a row or rowset. This can be achieved by passing a FROM clause to the select object. The first argument in the FROM clause is identical to that of a Zend_Db_Select object with the addition of being able to pass an instance of Zend_Db_Table_Abstract and have it automatically determine the table name.

Example 283. Retrieving specific columns


<?php
$table = new Bugs();

$select = $table->select();
$select->from($table, array('bug_id', 'bug_description'))
       ->where('bug_status = ?', 'NEW');

$rows = $table->fetchAll($select);

Important

The rowset contains rows that are still 'valid' - they simply contain a subset of the columns of a table. If a save() method is called on a partial row then only the fields available will be modified.

You can also specify expressions within a FROM clause and have these returned as a readOnly row or rowset. In this example we will return a rows from the bugs table that show an aggregate of the number of new bugs reported by individuals. Note the GROUP clause. The 'count' column will be made available to the row for evaluation and can be accessed as if it were part of the schema.

Example 284. Retrieving expressions as columns


<?php
$table = new Bugs();

$select = $table->select();
$select->from($table,
              array('COUNT(reported_by) as `count`', 'reported_by'))
       ->where('bug_status = ?', 'NEW')
       ->group('reported_by');

$rows = $table->fetchAll($select);

You can also use a lookup as part of your query to further refine your fetch operations. In this example the accounts table is queried as part of a search for all new bugs reported by 'Bob'.

Example 285. Using a lookup table to refine the results of fetchAll()


<?php
$table = new Bugs();

// retrieve with from part set, important when joining
$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
       ->where('bug_status = ?', 'NEW')
       ->join('accounts', 'accounts.account_name = bugs.reported_by')
       ->where('accounts.account_name = ?', 'Bob');

$rows = $table->fetchAll($select);

The Zend_Db_Table_Select is primarily used to constrain and validate so that it may enforce the criteria for a legal SELECT query. However there may be certain cases where you require the flexibility of the Zend_Db_Table_Row component and do not require a writable or deletable row. for this specific user case, it is possible to retrieve a row or rowset by passing a FALSE value to setIntegrityCheck(). The resulting row or rowset will be returned as a 'locked' row (meaning the save(), delete() and any field-setting methods will throw an exception).

Example 286. Removing the integrity check on Zend_Db_Table_Select to allow JOINed rows


<?php
$table = new Bugs();

$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
                ->setIntegrityCheck(false);
$select->where('bug_status = ?', 'NEW')
       ->join('accounts',
              'accounts.account_name = bugs.reported_by',
              'account_name')
       ->where('accounts.account_name = ?', 'Bob');

$rows = $table->fetchAll($select);

Select API

Warning

Fetching a rowset

Advanced usage

Important

Zend Framework