CHAPTER 5 ■ WORKING WITH DATABASES

Retrieving Query Results

There are various ways to retrieve query results depending on whether you need a single row or the whole result set, or whether you are planning to get a range of results for internal use or for display as a paged result set.

Getting a Single Value

If all you need from the database is a single value, you can use the ->fetchField() method to retrieve that value. Here is an example of retrieving the total number of records from the joke table:

$nbr_records = db_query("SELECT count(nid) FROM {joke}")->fetchField();

Getting Multiple Rows

In most cases, you will want to return more than a single field from the database. Here is a typical iteration pattern for stepping through the result set:

$type = 'page'; $status = 1;

$result = db_query("SELECT nid, title FROM {node} WHERE type = :type AND status = :status", array(

':type' => $type, ':status' => 1, ));

foreach ($result as $row) { echo $row->title."<br/>";

}

The preceding code snippet will print out the title of all published nodes that are of type page (the status field in the node table is 0 for unpublished nodes and 1 for published nodes). The call to db_query returns an array of results, with each element of the array being a row from the table that matches the criteria specified in the query. Using foreach I’m able to iterate through the result set array, and in the preceding case, print out the title of each of the nodes on a separate line.

Using the Query Builder and Query Objects

One of the new features that Drupal 7 provides is the ability to construct query objects using a query builder. In the previous examples, my queries were relatively simple, but what if I had more complex queries to write? That’s where the query builder using query objects comes in handy. Let me show you an example, and then I’ll build on the concept as I demonstrate the creation of more complex queries in Drupal 7.

In an earlier example, I created a query that selected values from the role table where the role ID was greater than or equal to 2. The query that I used is as follows:

94

CHAPTER 5 ■ WORKING WITH DATABASES

$result = db_query('SELECT name FROM {role} WHERE rid = :rid', array(':rid' => 2));

I’ll write the same query using a query object and the query builder. First I’ll create the query object by selecting the table that I want to use and assign an identifier to the table (the r) so I can reference fields from that table.

$query = db_select('role', 'r');

Next I’ll expand the query to include a condition that must be met (rid = 2) and the fields that I want returned from the query.

$query ->condition('rid', 2)

->fields('r', array('name'));

Finally I’ll execute the query and assign the result set to $result.

$result = $query->execute();

I’ll print out the results by iterating through the array returned from the query.

foreach($result as $row) { echo $row->name."<br/>";

}

Using the query object and query builder makes it easier to construct complex database queries. I’ll demonstrate how to use the query builder in the following examples.

Getting a Limited Range of Results

Executing queries that may return hundreds or even thousands of records is a risk that you’ll want to think about as you write queries. One of the mechanisms for minimizing that risk is to use the range modifier to restrict the maximum number of records returned by the query. An example might be a query that returns all nodes that are of the type “page.” If the site has thousands of nodes, the query may take a while to execute and the user might be overwhelmed by the volume of information. You can use the range modifier to restrict the number of rows returned by your query, alleviating the potential of long-running queries and too much information.

The following query adds the range modifier to the query by setting the offset (starting record) to 0 and the maximum number of rows to return to 100.

$query = db_select('node', 'n');

$query

->condition('type', 'page') ->fields('n', array('title')) ->range(0,100);

$result = $query->execute();

95

CHAPTER 5 ■ WORKING WITH DATABASES

foreach($result as $row) { echo $row->title."<br/>";

}

Getting Results for Paged Display

If your query returns a large number of rows, you may want to consider using a pager. A pager limits the number of rows displayed on the page while providing a navigational element that allows the site visitor to navigate, or page through the results. An example might be a query that returns 100 rows. You could configure the query to display the results 10 rows at a time with the ability to click on a “next” button to see the next 10 rows, “previous” to see the previous 10 rows, “first” to see the first 10 rows, “last” to see the last 10 rows, or by clicking on a page number to jump to that specific page of results (e.g., clicking on 5 would take the visitor to rows 51 through 60).

To demonstrate using a pager, I’ll create a query that returns all page nodes in the node table and displays the results with 10 rows per page with a pager at the bottom.

First I’ll create the query object and extend the query object by instructing Drupal to create a query object that uses a pager.

$query = db_select('node', 'n')->extend('PagerDefault');

Next I’ll add the condition, fields, and the number of items that I want to appear on a page using the limit modifier.

$query

->condition('type', 'page') ->fields('n', array('title')) ->limit(10);

Next I’ll execute the query and iterate through the result set, adding each row to an output variable that I’ve appropriately named $output.

$output = '';

foreach ($result as $row) { $output .= $row->title."<br/>";

}

Next I’ll call the theming function and apply the pager theme to my output, resulting in output that shows ten items per page with a pager at the bottom (see Figure 5-2), and display the results. For details on how the pager handles database results and the details of how the theme layer renders paged results, please see /includes/pager.inc.

$output .= theme('pager'); print $output;

96

CHAPTER 5 ■ WORKING WITH DATABASES

Figure 5-2. Drupal’s pager gives built-in navigation through a result set.

Other Common Queries

Drupal 7’s database layer provides a number of other common functions that you’ll likely want to use. The first example is sorting the result set. Using the orderBy method allows you to sort the result set. The example sorts the result set in ascending order by title.

$query

->condition('type', 'page') ->fields('n', array('title')) ->orderBy('title', 'ASC');

The next example modifies the sort by first sorting by the date the node was changed in descending order, followed by sorting the title in ascending order.

$query

->condition('type', 'page') ->fields('n', array('title', 'changed')) ->orderBy('changed', 'DESC') ->orderBy('title', 'ASC');

97