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

Something that has come up on one of our projects recently is when to use fetchAll<span style="color: #009900;">(</span><span style="color: #009900;">)</span> or equivalents like fetchAllAssoc<span style="color: #009900;">(</span><span style="color: #009900;">)</span>.

When writing a db_query() or db_select() query, you don't always need to use <span style="color: #000088;">$query</span><span style="color: #339933;">-></span><span style="color: #004000;">fetchAll</span><span style="color: #009900;">(</span><span style="color: #009900;">)</span> or <span style="color: #000088;">$query</span><span style="color: #339933;">-></span><span style="color: #004000;">fetchAllAssoc</span><span style="color: #009900;">(</span><span style="color: #009900;">)</span> 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 <span style="color: #b1b100;">foreach</span><span style="color: #009900;">(</span><span style="color: #009900;">)</span>. 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<span style="color: #009900;">(</span><span style="color: #009900;">)</span>:

<span style="color: #000000"><span style="color: #0000BB"><?php<br></span><span style="color: #FF8000">// Build and run the query.<br></span><span style="color: #0000BB">$results </span><span style="color: #007700">= </span><span style="color: #0000BB">db_select</span><span style="color: #007700">(</span><span style="color: #DD0000">'node'</span><span style="color: #007700">, </span><span style="color: #DD0000">'n'</span><span style="color: #007700">)<br>           -></span><span style="color: #0000BB">fields</span><span style="color: #007700">(</span><span style="color: #DD0000">'n'</span><span style="color: #007700">, array(</span><span style="color: #DD0000">'nid'</span><span style="color: #007700">, </span><span style="color: #DD0000">'title'</span><span style="color: #007700">))<br>           -></span><span style="color: #0000BB">condition</span><span style="color: #007700">(</span><span style="color: #DD0000">'status'</span><span style="color: #007700">, </span><span style="color: #0000BB">0</span><span style="color: #007700">, </span><span style="color: #DD0000">'!='</span><span style="color: #007700">)<br>           -></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br><br></span><span style="color: #0000BB">$nodes </span><span style="color: #007700">= array()<br></span><span style="color: #FF8000">// We can use a foreach loop on the $results object.<br></span><span style="color: #007700">foreach (</span><span style="color: #0000BB">$results </span><span style="color: #007700">as </span><span style="color: #0000BB">$n</span><span style="color: #007700">) {<br>  </span><span style="color: #FF8000">// Each $n is an object.<br>  </span><span style="color: #0000BB">$nodes</span><span style="color: #007700">[</span><span style="color: #0000BB">$n</span><span style="color: #007700">-></span><span style="color: #0000BB">nid</span><span style="color: #007700">] = </span><span style="color: #0000BB">$n</span><span style="color: #007700">-></span><span style="color: #0000BB">title</span><span style="color: #007700">;<br>}<br></span><span style="color: #0000BB">?></span></span>

If you need the result as an array, you can use <span style="color: #000088;">$results</span><span style="color: #339933;">-></span><span style="color: #004000;">fetchAssoc</span><span style="color: #009900;">(</span><span style="color: #009900;">)</span> to retrieve one item at a time:

<span style="color: #000000"><span style="color: #0000BB"><?php<br></span><span style="color: #FF8000">// Build and run the query.<br></span><span style="color: #0000BB">$results </span><span style="color: #007700">= </span><span style="color: #0000BB">db_select</span><span style="color: #007700">(</span><span style="color: #DD0000">'node'</span><span style="color: #007700">, </span><span style="color: #DD0000">'n'</span><span style="color: #007700">)<br>           -></span><span style="color: #0000BB">fields</span><span style="color: #007700">(</span><span style="color: #DD0000">'n'</span><span style="color: #007700">, array(</span><span style="color: #DD0000">'nid'</span><span style="color: #007700">, </span><span style="color: #DD0000">'title'</span><span style="color: #007700">))<br>           -></span><span style="color: #0000BB">condition</span><span style="color: #007700">(</span><span style="color: #DD0000">'status'</span><span style="color: #007700">, </span><span style="color: #0000BB">0</span><span style="color: #007700">, </span><span style="color: #DD0000">'!='</span><span style="color: #007700">)<br>           -></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br><br></span><span style="color: #0000BB">$nodes </span><span style="color: #007700">= array()<br></span><span style="color: #FF8000">// We can use a foreach loop on the $results object.<br></span><span style="color: #007700">while (</span><span style="color: #0000BB">$n </span><span style="color: #007700">= </span><span style="color: #0000BB">$results</span><span style="color: #007700">-></span><span style="color: #0000BB">fetchAssoc</span><span style="color: #007700">()) {<br>  </span><span style="color: #FF8000">// Each $n is an object.<br>  </span><span style="color: #0000BB">$nodes</span><span style="color: #007700">[</span><span style="color: #0000BB">$n</span><span style="color: #007700">[</span><span style="color: #DD0000">'nid'</span><span style="color: #007700">]] = </span><span style="color: #0000BB">$n</span><span style="color: #007700">[</span><span style="color: #DD0000">'title'</span><span style="color: #007700">];<br>}<br></span><span style="color: #0000BB">?></span></span>

One common use-case for using fetchAll<span style="color: #009900;">(</span><span style="color: #009900;">)</span> is so you can use <a href="http://www.php.net/count"><span style="color: #990000;">count</span></a><span style="color: #009900;">(</span><span style="color: #000088;">$results</span><span style="color: #009900;">)</span> to find out how many items were returned by the query. To get around this, you can use a count query instead. So:

<span style="color: #000000"><span style="color: #0000BB"><?php<br></span><span style="color: #FF8000">// Create the query, but don't execute it yet.<br></span><span style="color: #0000BB">$query </span><span style="color: #007700">= </span><span style="color: #0000BB">db_select</span><span style="color: #007700">(</span><span style="color: #DD0000">'node'</span><span style="color: #007700">, </span><span style="color: #DD0000">'n'</span><span style="color: #007700">)<br>         -></span><span style="color: #0000BB">fields</span><span style="color: #007700">(</span><span style="color: #DD0000">'n'</span><span style="color: #007700">, array(</span><span style="color: #DD0000">'nid'</span><span style="color: #007700">, </span><span style="color: #DD0000">'title'</span><span style="color: #007700">))<br>         -></span><span style="color: #0000BB">condition</span><span style="color: #007700">(</span><span style="color: #DD0000">'status'</span><span style="color: #007700">, </span><span style="color: #0000BB">0</span><span style="color: #007700">, </span><span style="color: #DD0000">'!='</span><span style="color: #007700">);<br><br></span><span style="color: #FF8000">// This runs a COUNT(*) query to count the number of results.<br></span><span style="color: #0000BB">$count </span><span style="color: #007700">= </span><span style="color: #0000BB">$query</span><span style="color: #007700">-></span><span style="color: #0000BB">countQuery</span><span style="color: #007700">()-></span><span style="color: #0000BB">execute</span><span style="color: #007700">()-></span><span style="color: #0000BB">fetchField</span><span style="color: #007700">();<br></span><span style="color: #FF8000">// Now run the original query and return the result set.<br></span><span style="color: #0000BB">$results </span><span style="color: #007700">= </span><span style="color: #0000BB">$query</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br></span><span style="color: #0000BB">?></span></span>

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

Original Article:

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