Drupal7 Database API: select (readonly) queries and processing the results

Keywords

On db_query():

Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.

Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.

From Dynamic queries:

Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string. All Insert, Update, Delete, and Merge queries must be dynamic. Select queries may be either static or dynamic. Therefore, "dynamic query" generally refers to a dynamic Select query.

Note: in 90% of select query use cases you will have a static query. If in a critical performance path, you should use db_query() and friends instead of db_select() for performance reasons. Only use dynamic queries if the query parts vary (example: adding WHERE conditions depending on the context) or if they should be alterable (example: node access). Note: Varied opinions exist on this ...

...

// Create an object of type SelectQuery and directly 
// add extra detail to this query object: a condition, fields and a range
$query = db_select('users', 'u')
  ->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

...

Once the query is built, call the execute() method to compile and run the query.

$result = $query->execute();

The execute() method will return a result set / statement object that is identical to that returned by db_query(), and it may be iterated or fetched in the exact same way:

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

...

Debugging
To examine the SQL query and the arguments that the query object uses at a particular point in its lifecycle, call its __toString() method:

print_r($query->__toString());
print_r($query->arguments());
The Result sets docs are a good primer to processing the results of a query, and on the differences between:
$record = $result->fetch();            // Use the default fetch mode.
$record = $result->fetchObject();  // Fetch as a stdClass object.
$record = $result->fetchAssoc();   // Fetch as an associative array.

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

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

// 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;
}
Visit also