Calling query() on a driver or prepared statement will return a recordset. The data can be fetched row-by-row via an iterator or all at once as an array. Each row of the recordset can only be traversed one time, and must be done in order.

Naming conventions

There are many different methods associated with the recordset, depending on how you want the data returned (associative array, indexed array, etc) and if you want an array or an iterator.

The methods are named after the following conventions:

  • fetch returns data. When combined with All, Column, or Keyed, it will return the remainder of the resultset as an array. Otherwise, it is only returning something from the next row.
  • get*Iterator returns an iterator that returns one row at a time. This is the best way to get data if you simply need to process records.
  • The “default” row type is an associative array.
  • The methods with an explicit Array return an indexed array.
  • The “keyed” methods return some sort of associative array where the key comes from data in the column.

All of the methods are listed below, followed by an overview of the main differences between them. Then the keyed methods and callbacks are covered in more detail.

Fetching one record

Type Row Type Method
return by value one value from single row fetchValue($i = 0) : string
return by value associative array fetchRow() : array
return by value indexed array fetchRowAsArray() : array
modify by reference associative array fetchRowInto(&$array) : boolean
modify by reference indexed array fetchRowIntoArray(&$array) : boolean
modify by reference object fetchRowIntoObject(&object) : boolean

Fetching multiple records

Key Row Type Method
indexed associative array fetchAll() : array
indexed indexed array fetchAllAsArray() : array
indexed callback fetchAllWithCallback($cb) : array
indexed scalar fetchColumn($i = 0) : array
associative associative array fetchKeyed($key = '') : array
associative indexed array fetchKeyedAsArray($i = 0) : array
associative callback fetchKeyedWithCallback($cb, $key = '') : array


Key Row Type Method
indexed associative array getIterator() : iterator
indexed indexed array getArrayIterator() : iterator
indexed callback getCallbackIterator($cb) : iterator
indexed scalar getColumnIterator($i = 0) : iterator
associative associative array getKeyedIterator($key = '') : iterator
associative indexed array getKeyedArrayIterator($i = 0) : iterator
associative callback getKeyedCallbackIterator($cb, $key = '') : iterator

Fetching data

The most direct way to fetch data is via the default iterator:

foreach ($driver->query($sql) as $row)
    echo $row['columnName'], PHP_EOL;

This is equivalent to:

foreach ($driver->query($sql)->getIterator() as $row) ...

Single value

If you only need a single value from a row, you can use fetchValue().

$value = $driver->query('SELECT COUNT(*) FROM t1')->fetchValue();

It defaults to using the first column, but you can specifiy a different column by passing the index (not the name of column). But whenever possible, it’s better to just rewrite the SQL to only return the column you are interested in.

Single row

If you only need a single row, then you can use fetchRow() or fetchRowAsArray().

$row = $driver->query($sql)->fetchRow();
echo $row['columnName'], PHP_EOL;

$row = $driver->query($sql)->fetchRowAsArray();
echo $row[0], PHP_EOL;


Of course, these fetch value and row methods can be used multiple times on the same recordset. But usually it’s much more succinct to use the related iterator or fetchAll/Column methods.

Fetching by reference

It’s also possible to fetch into an array or object. These methods return true (more records) or false (no more records).

$rs = $driver->query($sql);

while ($rs->fetchInto($row))
    echo $row['columnName'], PHP_EOL;

This is the only way to fetch into an object (other than by custom callbacks):

$rs = $driver->query($sql);

$object = new stdClass;

while ($rs->fetchIntoObject($object))
    echo $object->columName, PHP_EOL;

In both cases, the supplied array or object will have its elements or properties overridden. Anything that does not exist in the row will not be modified.

The entire recordset

The fetchAll/Column/Keyed methods return the entire recordset (or more accurately the remainder of the recordset) as an array. This is useful when you need the data as an array, so that you can perform arbitrary lookups or iterate through it multiple times.

$results = $driver->query($sql)->fetchAll();


It’s possible to mix things together:

$q = $driver->query($sql);

$firstRow = $q->fetchRow();
$remainingRows = $q->fetchAll();

The data will not be duplicated, as you can only iterate over each row one time.


The iterators allow you to iterate over the recordset with the format that best suits your needs. When iterating directly over the recordset, you are using the getIterator() method implicitly.

To use indexed arrays:

foreach ($driver->query($sql)->getArrayIterator() as $row)
    echo $row[0], PHP_EOL;

To iterate over the nth column:

foreach ($driver->query($sql)->getColumnIterator() as $value)
    echo $value, PHP_EOL;

Keyed arrays

All of the keyed methods allow you to specify a column to use as the key, as opposed to a zero-based array. Imagine a dataset that looks like:

n en sp
1 one uno
2 two dos

With SELECT * and fetchAll, you would get an array just like that:

$driver->query('SELECT * FROM t1')->fetchAll()

  0 => ['n' => '1', 'en' => 'one', 'sp' => 'uno'],
  1 => ['n' => '2', 'en' => 'two', 'sp' => 'dos']

But say you want the n to be the key of the array. In that case, you need to use one of the keyed methods.

$driver->query('SELECT * FROM t1')->fetchKeyed()

  1 => ['en' => 'one', 'sp' => 'uno'],
  2 => ['en' => 'two', 'sp' => 'dos']

The keyed value is removed from the row. If there is only one value left, then a simple key => value array is returned.

$driver->query('SELECT n,en FROM t1')->fetchKeyed()

  1 => 'one',
  2 => 'two'

If there are no more columns left, then the true is used for the value.

$driver->query('SELECT en FROM t1')->fetchKeyed()

  'one' => true,
  'two' => true

The keyed iterator works the same way:

foreach ($driver->query('SELECT n,en')->getKeyedIterator() as $n => $en)
    echo $n, ': ', $en, PHP_EOL;


For more flexibility in how the records are returned, use a callback. Note that there is no way to directly return an object. This introduces complexities (constructor parameters, injecting dependencies, etc) that are better handled by a callback.

Callbacks always receive the entire row as an associative array. They are free to return anything.

The following illustrates using a callback to handle custom object creation:

$cb = function (array $row) {
    $myObject = new MyObject;
    return $myObject;

foreach ($driver->query($sql)->getCallbackIterator($cb) as $myObject)
    echo $myObject->getFirstName(), PHP_EOL;

Or to retrieve them all in an array:

$myObjects = $driver->query($sql)->fetchAllWithCallback($cb);


There is no single row callback because you could just as easily do this:

$myObject = $cb($driver->query($sql)->fetchRow());

Keyed callbacks

Callbacks can also be used with keyed queries. As with regular queries, those callbacks retreive the entire row as an associative array. However, keyed callbacks return an array with the key and value.