When to use $query->fetchAll() (hint: it's optional)

Mark Pavlitski's picture
Jan 8th 2015Technical Director

Something that has come up on one of our projects recently is when to use fetchAll() or equivalents like fetchAllAssoc().

When writing a db_query() or db_select() query, you don't always need to use $query->fetchAll() or $query->fetchAllAssoc() to retrieve the result set; it's optional.

Database queries in Drupal 7 are iterator objects, so you can treat them like an array and loop over the results with foreach(). If you're going to do any processing on the results, it'll be quicker and use much less memory this way.

So for example, this will still work, note the lack of fetchAll():

<?php
// Build and run the query.
$results = db_select('node', 'n')
           ->fields('n', array('nid', 'title'))
           ->condition('status', 0, '!=')
           ->execute();

$nodes = array();

// We can use a foreach loop on the $results object.
foreach ($results as $n) {
  // Each $n is an object.
  $nodes[$n->nid] = $n->title;
}
?>

If you need the result as an array, you can use $results->fetchAssoc() to retrieve one item at a time:

<?php
// Build and run the query.
$results = db_select('node', 'n')
           ->fields('n', array('nid', 'title'))
           ->condition('status', 0, '!=')
           ->execute();

$nodes = array();

// We can use a foreach loop on the $results object.
while ($n = $results->fetchAssoc()) {
  // Each $n is an object.
  $nodes[$n['nid']] = $n['title'];
}
?>

One common use-case for using fetchAll() is so you can use count($results) to find out how many items were returned by the query. To get around this, you can use a count query instead. So:

<?php
// Create the query, but don't execute it yet.

$query = db_select('node', 'n')
         ->fields('n', array('nid', 'title'))
         ->condition('status', 0, '!=');


// This runs a COUNT(*) query to count the number of results.
$count = $query->countQuery()->execute()->fetchField();
// Now run the original query and return the result set.
$results = $query->execute();
?>

More details can be found in the DatabaseStatementInterface API documentation, or in the PHP PDO manual.

Sophie Shanahan-Kluth's picture

You may also like...

Omega 4 Theme Tutorial

Sophie Shanahan-Kluth, Sep 2nd 2014
A comparison between Omega 3 and Omega 4, plus how to create an Omega 4 subtheme and set up Sass for the first time.

Comments

Gonzalo

Jan 21st 2016 - 8:01pmreply
Gonzalo's picture

Thx for this hint :D

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Related Blogs

Sophie Shanahan-Kluth's picture
Rick Donohoe's picture
Mark Pavlitski's picture