Query results

Select queries always return a result set object containing zero or more records. There are several ways to retrieve data from this result set depending on the use case. Records are fetched as objects by default unless you change the fetch mode (see: setFetchMode).

The most common use case is iterating over the result set with a foreach() loop.

// Static query:
$result = $connection->query("SELECT field1, field2 FROM {mytable}");
foreach ($result as $record) {
  // Do something with each $record
}

// Dynamic query
$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
}

However, depending on what the results are needed for, there are several other ways to retrieve records.

To explicitly fetch the next record, use:

$record = $result->fetch();            // Use the default fetch mode.
$record = $result->fetchObject();  // Fetch as a stdClass object.
$record = $result->fetchAssoc();   // Fetch as an associative array.

If there is no next record, FALSE is returned. You should generally avoid fetch() in favor of fetchObject() and fetchAssoc(), as the latter are more self-documenting. If you need to use another PDO-supported fetch mode, use fetch().

To fetch only a single field from the result set, use:

$record = $result->fetchField($column_index);

The default $column_index value is 0 for the first field.

To count the number of rows affected by a DELETE, INSERT, or UPDATE statement, use:

$number_of_rows = $result->rowCount();

To count the number of rows returned by a SELECT statement, use:

$number_of_rows = $connection->select('mytable')->countQuery()->execute()->fetchField();

To fetch all records at once into a single array, use one of the following:

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();

// Retrieve all records into an associative array keyed by a field from the result.
$result->fetchAllAssoc($field);

// Retrieve a 2-column result set as an associative array of field 0 => field 1.
$result->fetchAllKeyed();
// You can also specify which two fields to use by their column numbers.
$result->fetchAllKeyed(0, 2); // field 0 => field 2
$result->fetchAllKeyed(1, 0); // field 1 => field 0
// For checkboxes or similar cases:
$result->fetchAllKeyed(0, 0); // field 0 => field 0, e.g., [article] => [article]

// Retrieve a 1-column result set as a single array.
$result->fetchCol();
// Specify a column index to fetch from:
$result->fetchCol($column_index);

Note that fetchAll() and fetchAllAssoc() will by default use the fetch mode set on the query (numeric array, associative array, or object). You can override this by passing a new fetch mode constant. For fetchAll(), this is the first parameter. For fetchAllAssoc(), it’s the second parameter. Examples:

// Get an array of arrays keyed on the field 'id'.
$result->fetchAllAssoc('id', PDO::FETCH_ASSOC);
// Get an array of arrays with both numeric and associative keys.
$result->fetchAll(PDO::FETCH_BOTH);

Since PHP supports method chaining on returned objects, it’s common to omit the $result variable entirely, like so:

// Get an associative array of ids to titles.
$examples = $connection->query("SELECT id, title FROM {mytable}")->fetchAllKeyed();

// Get a single record from the database.
$myobj = $connection->query("SELECT * FROM {mytable} WHERE example = :example", [':example' => $example])->fetchObject();

// Get a single value from the database.
$myval = $connection->query("SELECT example FROM {mytable} WHERE id = :id", [':id' => $id])->fetchField();

If you need a simple array like [1, 2, 3, 4, 5], you’ll likely end up with something more like [1 => 1, 2 => 2, 3 => 3, 4 => 4, 5 => 5]. You can get this using:

$ids = $connection->query("SELECT id FROM {example}")->fetchAllKeyed(0, 0);