Last updated August 19, 2016. Created on August 16, 2011.
Edited by dzutaro, komlenic, jenlampton, joachim. Log in to edit this page.

A Select query will always return a result set object of zero or more records. There are several ways to then retrieve data from that result set, depending on the use case.

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

$result = db_query("SELECT nid, title FROM {node}");
foreach ($result as $record) {
  // Do something with each $record
  $node = node_load($record->nid);

Depending on what the results are needed for, however, there are a number of other ways to retrieve the 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 will be returned. fetch() should generally be avoided in favor of fetchObject() and fetchAssoc(), as the latter are more self-documenting. If you need to use some other PDO-supported fetch mode, then use fetch().

To fetch just a single field out of the result set, use:

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

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

To count the number of rows returned from a DELETE, INSERT or UPDATE statement use:

$number_of_rows = $result->rowCount();

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

$number_of_rows = db_select('node')->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.

// Retrieve all records into an associative array keyed by the field in the result specified.

// Retrieve a 2-column result set as an associative array of field 0 => field 1.
// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0
// If you need an array where keys and values contain the same field (e.g. for creating a 'checkboxes' form element), the following is a perfectly valid method:
$result->fetchAllKeyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]

// Retrieve a 1-column result set as one single array.
// Column number can be specified otherwise defaults to first column

Note that fetchAll() and fetchAllAssoc() will by default fetch using whatever fetch mode was set on the query (numeric array, associative array, or object). That can be modified by passing in a new fetch mode constant. For fetchAll(), it is the first parameter. For fetchAllAssoc(), it is 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.

Because PHP supports chaining method calls on returned objects, it is very common to skip the $result variable entirely, like so:

// Get an associative array of nids to titles.
$nodes = db_query("SELECT nid, title FROM {node}")->fetchAllKeyed();

// Get a single record out of the database.
$node = db_query("SELECT * FROM {node} WHERE nid = :nid", array(':nid' => $nid))->fetchObject();

// Get a single value out of the database.
$title = db_query("SELECT title FROM {node} WHERE nid = :nid", array(':nid' => $nid))->fetchField();

If what you want is a simple array like array(1, 2, 3, 4, 5) you will have to settle for something more like array(1=>1, 2=>2, 3=>3, 4=>4, 5=>5). You can get this by using

$nids = db_query("SELECT nid FROM {node}")->fetchAllKeyed(0,0);

Looking for support? Visit the forums, or join #drupal-support in IRC.


ezheidtmann’s picture

I think there is a typo in this section:

// Retrieve a 2-column result set as an associative array of field 1 => field 2.

Shouldn't the comment read, "field 0 => field 1", as implied by this snippet?

Get an associative array of nids to titles.
$nodes = db_query("SELECT nid, title FROM {node}")->fetchAllKeyed();
drupalshrek’s picture

While testing I found it useful to exit the program and display rowCount():

 exit("There were: " . $result->rowCount() . " rows returned.");


ssm2017 Binder’s picture

Note that requested field names are set to lowercase.
For example, if your database field is named : FolderID, the resulted object will be $record->folderid

aquariumtap’s picture

See the change notice. Results are no longer forced to lowercase.

takinola’s picture

When you run a database query and then read the results by using a foreach loop, if you run another foreach loop over the query results, you will get an empty set (see example below). Somehow, running a foreach loop over a database result object makes it impossible to run another foreach loop over that same object.

$query = db_query("SELECT field1 FROM {db_table} WHERE field2 = :var", array(':var'=>$var));

print "first set";
foreach($query as $key1=>$a){
    print $a->field1;

print "second set";
foreach($query as $key2=>$b){
    print $b->field1;

will result in something like this

first set
<field1 value>
<field1 value>
<field1 value>

second set

The second foreach loop will find no data!

loganphp’s picture

Did you get any solution for this issue? In many situation I want to iterate result set more than one time I don't know what do that time. So I write query again to do it I know it is really bad code.

apotek’s picture

Even though you are iterating with foreach(), $result is not an array. It is a database result object that just happens to implement an iterator.

In a normal array, all members of the array have a value, and you can get each value by iterating over the array one member at a time. When you get to the bottom of the array, you can either reset($array) and iterate again, or (if you only use foreach()), just foreach($array) again.

However, the database result object does not *contain* all the returned rows in the manner an array does. When you iterate over the database result object with a foreach(), you are actually fetching each value from the result object as you iterate. If you don't store this value somewhere in a php variable, it is fetched and gone from the result object, and there is nothing inside the $result object to retrieve the second time you try to foreach() it.

The benefit of using a db result object is that it requires less php memory since it doesn't store all your values in a php variable. So if you are expecting a lot of rows, iterate over a db result set.

However, if you need to iterate more than once, or maybe don't have so many rows coming back at you, then you can use the fetchAll() / fetchCol() / etc methods to get all the results from the db at once, dumped into a regular php array that you can iterate over and over to your heart's content.

mah ham

wizonesolutions’s picture

I don't think $result->fetchObject() actually works except for SQlite queries. I can't find the implementation in the code, at least.

So perhaps that should be removed?

FillPDF Service - - Hosted solution for FillPDF

joshf’s picture

The PDO drivers for MySQL and Postgres provide this method natively. SQLite was just a gap that needed to be filled.

ganeshtodkar’s picture

db_select($table, $alias = NULL, array $options = array())


$result = db_select('users', 'u')

Check this post.
Hope this helps.
Thank you.

jefkin’s picture

Sometimes you want to get the many of a one to many relationship, for instance: You need an admin page that lists all the permissions that are defined for a given role (rid). Of course you can build a query and loop over the results, but there are quicker ways:

$rid = 8;
$query =                                           //  Run a query to;
    db_select('role_permission',  'rp')            //      select from the permission table,
        ->condition('rp.rid', $rid)                //      where the rid matches our role,
        ->fields('rp', array ( 'permission' ))     //      retrieve the permission field.
$perms = array_keys($query->fetchAllKeyed(0, 0));  //  Get the results in a standard array.
jenlampton’s picture

*edit* decided to edit the main post instead