Working with a Database in Drupal 7 - Lesson 7 - Processing Query Results (fetch)

A SELECT query will always return a result that contains zero or more rows. There are several ways to process query results, and you can choose the one that best fits your needs.

The most common way is using a foreach() loop:

nid);
}
?>

Depending on what you need to do with the result set, you can use other methods for fetching records. To explicitly fetch the next record, use:

fetch();           // Default fetch (as object).
$record = $result->fetchObject();     // Fetch as object.
$record = $result->fetchAssoc();      // Fetch as associative array.
?>

If there are no more records, these methods will return FALSE. It is recommended to use fetchObject() or fetchAssoc() instead of fetch() to clearly define the expected result format. To fetch a single field from one row:

fetchField($column_index);
?>

The default value for $column_index is 0. To count the number of rows in the result set:

rowCount();
?>

To fetch all rows into an array:

fetchAll();

// Fetch result as associative array using a specific field as the key.
$result->fetchAllAssoc($field);

// Fetch a key-value array: column 1 => column 2
$result->fetchAllKeyed();
$result->fetchAllKeyed(0, 2); // array will be column 0 => column 2
$result->fetchAllKeyed(1, 0); // array will be column 1 => column 0

// Fetch a single column as an array.
$result->fetchCol();
// Count values from a specific column (default is the first).
$result->fetchCol($column_index);
?>

Note that fetchAll() and fetchAllAssoc() will use the current fetch mode setting. You can change the fetch mode by passing a new fetch mode constant. For fetchAll(), it's the first parameter. For fetchAllAssoc(), it's the second.

Because PHP supports method chaining, you can skip assigning the result to a variable:

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

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

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