RecordSets¶

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`

Iterators¶

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;

Note

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();

Note

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.

Iterators¶

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;
}

Callbacks¶

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;
    $myObject->setFirstName($row['first_name']);
    $myObject->setLastName($row['last_name']);
    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);

Note

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.