In the db_query() function, all table names must be wrapped in curly braces. This is required so that Drupal can add a prefix string to the table name if it needs to. It's possible to share a single database instance with several Drupal installations. Insuch cases, you can establish a prefix that will be placed on each database table to distinguish which instance uses which set of tables. In your SELECT statement, then, you just need to put the generic name of the table, and if you add curly braces, the db_query() function will apply the appropriate table prefix for your site.

Placeholders are abstractions you can place in your query string to indicate the value of variables that will be replaced when the query string is parsed by the db_query() function. This abstraction allows the database to differentiate between raw SQL syntax and values provided by the user, thereby avoiding SQL injection.

Placeholders are set apart from the query by prefixing a string with a colon. The value of all placeholders must then be defined by creating a keyed array with their values, as shown in Listing 9-5.

global $user;
$uid = $user->uid;
$result = db_query (

'SELECT n.nid, n.title, n.created '
  . 'FROM {node} n '
  . 'WHERE n.uid = :uid AND n.created > :created'
  , array(
    ':uid' => $uid,
    ':created' => REQUEST_TIME - 3600,
    )
  );

  foreach ($result as $record) {
    $nid = $record->nid;
    $title = $record->title;
    $created = $record->created;
  }

This returns all nodes that have been created in the past hour (3,600 seconds). The REQUEST_TIME variable is a handy shortcut for the time the page is being processed (i.e., now).

Here are some important safety tips when using placeholders:

$result = db_query (
  'SELECT title, created '
  . 'FROM {node} '
  . 'WHERE uid IN (:uids)'
  , array(
      ':uids' => array(1, 3, 5, 7, 11),
      )
    );

foreach ($result as $record) {
  $title = $record->title;
  $created = $record->created;
}

The most common technique for accessing the record set returned by the query is by using the foreach() loop as in the preceding examples. There are other ways to fetch the records, though, depending on your situation. And there are three different functions to explicitly fetch the next record, depending on how you want to deal with the data.

Be sure to check that the return value from the fetch functions is not FALSE before proceeding, which indicates you've reached the end of the record set.

One technique you'll see as you explore the Drupal core code is a shortcut made possible by PHP's support for chaining method calls. Listing 9-7 shows an example of how to access just a single field by querying the database and chaining the methods.

// Get a keyed array of all nodes
$nodes = db_query("SELECT nid, title FROM {node}")->fetchAllKeyed();

// Extract just the title of a node
$title = db_query(
  'SELECT title FROM {node} '
  . 'WHERE nid = :nid',
  array(':nid' => $nid))->fetchField();

Now, why don't I like static queries? Because they send SQL statements from your module to the database. The designers of Drupal's database layer have done a pretty good job of sanitizing the database inputs using the escaped fields, so there's not much to worry about security-wise. But there is the issue of cross-database portability.

Consider a simple query to select the first ten records. For MySQL, the LIMIT keyword is used in the predicate:

SELECT title FROM table LIMIT 0, 10

For SQL Server, the TOP keyword is used:

SELECT TOP 10 title FROM table

If you are accustomed to working with MySQL, your query might look like the example in Listing 9-8.

$nodes = db_query('SELECT title FROM {node} LIMIT 0, 10')->fetchObject();

But what would happen if this query was executed against an underlying SQL Server database? It would break.

For this reason, the database layer has a range query function that can be used to act as a layer between your code and the database engine. The syntax for using the query is shown in Listing 9-9.

$result = db_query_range('SELECT title FROM {node}', 0, 10);

Drupal 7 encapsulates this functionality, along with much more, into a more abstract query syntax, dynamic queries, which I'll describe in the next section.

Creating a dynamic query for a SELECT requires providing the table name and some optional parameters as shown in Listing 9-10.

$query = db_select('node', 'n'),
$query
  ->condition('n.uid', 0, '<>')
  ->fields('n', array('nid', 'title', 'type', 'created'))
  ->range(0, 5);
$result = $query->execute();

foreach ($result as $record) {
  $title = $record->title;
  $type = $record->type;
  $created = $record->created;
  print($title . ' (' . $type . ') created: '
    . date("D, j M, Y a\t G:i", $created) . '<br>'),
}

This gives us the output shown in Figure 9-2.

Figure 9.2. Simple SELECT using a dynamic query

The db_select() function is where we indicate the table we are using. There is no need to wrap the name in curly braces as in the preceding static query because the function already knows that's the table name. The second parameter is an alias for the table.

The condition property is pretty self-explanatory; I want nodesonly where the user ID is not zero.

The fields property lists the fields I want returned, and starts with the alias declared in the db_select() function.

Finally, the range property limits the record set to the first five records.

This simple example will produce different SELECT statements depending on the database engine. For MySQL, the query will look like this:

SELECT n.nid, n.title, n.type, n.created FROM node n WHERE n.uid <> 0 LIMIT 0, 5;

For SQL Server, it will generate a different SELECT statement:

SELECT TOP 5 n.nid, n.title, n.type, n.created FROM node n WHERE n.uid <> 0;

So we've solved the problem of cross-database incompatibility and made our code easier to read, test, and debug at the same time.

When you join two or more tables, a number of properties can define how the join is to be done.

Consider the task of getting information out of the node and user tables, which requires doing a join of two tables. Listing 9-11 shows how to do this with a static query.

$result = db_query('SELECT u.name, u.mail n.title, n.type '
  . 'FROM {node} n '
  . 'INNER JOIN {users} u on u.uid = n.uid'
  . 'WHERE u.uid=:uid'.
  array(':uid' => $user->uid));

Listing 9-12 shows the same thing with a dynamic query.

$query = db_select('node', 'n'),
$query
  ->condition('u.uid', $user->uid, '=')
  ->fields('u', array('name', 'mail'))
  ->fields('n', array('title', 'type'))
  ->join('users', 'u', 'u.uid = n.uid'),
$result = $query->execute();

This gives us the output shown in Figure 9-3.

Figure 9.3. Output from a dynamic query with join

There are other combinations and permutations of the joins that you can read about in the Drupal documentation, but this should be enough to get you started.

In the code in Listing 9-12, you'll notice the fields() property. This property is used to indicate the columns you want returned from your query. The alias is specified first, followed by an array of field names corresponding to the column names in your table.

Notice that Listing 9-12 has two fields() methods. This is necessary because the query defines two different aliases. There is no room, however, to create field aliases using this syntax. If you want to have field aliases, you can use the addField() method for each field as shown in Listing 9-13.

global $user;
$query = db_select('node', 'n'),
$query
  ->condition('u.uid', $user->uid, '=')
  ->join('users', 'u', 'u.uid = n.uid'),
$query->addField('u', 'name', 'user_name'),
$query->addField('u', 'mail', 'user_mail'),
$query->addField('n', 'title', 'node_title'),
$query->addField('n', 'type', 'node_type'),

$result = $query->execute();

foreach ($result as $record) {
  $name = $record->user_name;
  $mail = $record->user_mail;
  $title = $record->node_title;
  $type = $record->node_type;
  print($title . ' (' . $type . ') , by ' . $name . ' (' . $mail . ')' . '<br>'),
}

This results in the same output as the previous example, and builds a SELECT statement as shown in Listing 9-14.

SELECT
  u.name AS user_name,
  u.mail AS user_mail,
  n.title AS node_title,
  n.type AS node_type
FROM
  {node} n
INNER JOIN
  {users} u ON u.uid = n.uid
WHERE
  (u.uid = :db_condition_placeholder_0)

Besides being able to assign aliases to fields, the addField() function is handy if you are building a query using some looping construct. Before this capability, you had to use the string concatenation method to build up a list of fields.

If you wish to limit your record set to a distinct set of values, you can use the distinct() property as shown in Listing 9-15.

$query = db_select('node', 'n'),
  $query
    ->fields('n', array('title'));
    $query->distinct();
    $result = $query->execute();

Expressions can be added to a query if you need to express something that is not otherwise available, as Listing 9-16 shows.

$query = db_select('node', 'n'),
$query
  ->condition('n.created', time() - (14 * 86400), '>')
  ->fields('n', array('title', 'created', 'type'));
$query->addExpression('created - :offset', 'offset_by_an_hour', array(':offset' => 3600));
$result = $query->execute();

foreach ($result as $record) {
  $name = $record->title;
  $type = $record->type;
  $created = $record->created;
  $offset = $record->offset_by_an_hour;
  print($name . ' (' . $type . ')'
    . ' created: ' . date("D, j M, Y a\t G:i", $created)
    . ' hour before: ' . date("D, j M, Y a\t G:i", $offset)
    . '<br>'),
}

This inserts a computed field that is the value of the created timestamp minus one hour, and returns all nodes created in the past fortnight (14 * 86400). (See Figure 9-4.)

Figure 9.4. Output from the query that used an expression

You use the orderBy() method to specify the collation order of a record set. Listing 9-17 shows an example.

$query = db_select('node', 'n')
  ->condition('n.created', time() - (14 * 86400), '>'),
$query->fields('n', array('title', 'type', 'created'));
$query->orderBy('title', 'ASC'),
$result = $query->execute();

foreach ($result as $record) {
  $name = $record->title;
  $type = $record->type;
  $created = $record->created;
  print($name . ' (' . $type . ')'

. ' created: ' . date("D, j M, Y a\t G:i", $created)
    . '<br>'),
}

This creates the output shown in Figure 9-5.

Figure 9.5. Ordering a result set

You can also specify a random ordering by using the orderRandom() method. It is possible to chain ordering and random ordering together, as shown in Listing 9-18.

$query = db_select('block', 'b'),
$query
  ->condition('b.region', −1, '<>')
  ->fields('b', array('region', 'module', 'delta'));
$query->orderBy('module')->orderRandom();
$result = $query->execute();

foreach ($result as $record) {
  $region = $record->region;
  $module = $record->module;
  $delta = $record->delta;
  print($region . ', ' . $module . ', ' . $delta . '<br>'),
}

This orders first by region and then randomly orders the records when there are two or more records with the same region, as shown in Figure 9-6.

Figure 9.6. Output from the random order method

Some notes on the orderBy() method:

Use the groupBy() method to group by a given field. This is demonstrated in Listing 9-19.

$query = db_select('block', 'b'),
$query
  ->condition('b.region', −1, '<>')
  ->fields('b', array('region', 'module', 'delta'));
$query->addExpression('COUNT(region)', 'count'),
$query->orderBy('region'),
$query->orderBy('module'),
$query->groupBy('region'),
$result = $query->execute();

foreach ($result as $record) {
  $region = $record->region;
  $module = $record->module;
  $delta = $record->delta;
  $count = $record->count;
  print('(' . $count . ') ' . $region . ', ' . $module . ', ' . $delta . '<br>'),
}

Notice that this example also uses the addExpression() method to add a count of records that are consumed in each group. The output of this code fragment is shown in Figure 9-7.

Figure 9.7. Output from the groupBy() example

Some notes on the groupBy() method:

The range() property can be used to limit the query to a certain number of records. It is called with two parameters that indicate the record to start with and the number of records to return from that point. An example is shown in Listing 9-20.

$query = db_select('role', 'r'),
$query
  //->condition('p.module', 'node', '=')
  ->condition('r.name', 'administrator', '<>')
  ->fields('r', array('name'))
  ->fields('p', array('permission', 'module'))
  ->join('role_permission', 'p', 'r.rid = p.rid')
;
$query->orderRandom();
//$query->orderBy('name'),
//$query->orderBy('module'),

//$query->orderBy('permission'),
$query->range(5, 8);
$result = $query->execute();

foreach ($result as $record) {
  print($record->name . ', ' . $record->module . ', ' . $record->permission . '<br>'),
}

This limits the query to return eight records, starting with the fifth one. The output is shown in Figure 9-8.

Figure 9.8. Output from a range-limited query

Some notes on the range() method:

The countQuery() method returns the number of rows returned by any query. Listing 9-21 shows an example.

$query = db_select('role_permission', 'p'),
$query
  ->condition('p.module', 'node', '=')
  ->fields('p', array('permission'))
;
$num_rows = $query->countQuery()->execute()->fetchField();

This outputs what you'd expect, as Figure 9-9 shows.

Figure 9.9. Counting rows

Another advantage of dynamic queries over static queries is that other modules can alter the query before it is executed against the database. Since the query is expressed in a Drupal-smart form instead of in raw SQL, we can tag the query with metadata that lets us identify a query that another module might be interested in.

This tagging is done with the addTag() method, as shown in Listing 9-22.

$query = db_select('role', 'r'),
$query
  ->condition('p.module', 'node', '=')
  ->condition('r.name', 'administrator', '=')
  ->fields('r', array('name'))
  ->fields('p', array('permission', 'module'))
  ->join('role_permission', 'p', 'r.rid = p.rid')
;
$query->orderBy('name'),
$query->orderBy('module'),
$query->orderBy('permission'),
$query->addTag('get_permissions'),
$result = $query->execute();

foreach ($result as $record) {
  print($record->name . ', ' . $record->module . ', ' . $record->permission . '<br>'),
}

The output, shown in Figure 9-10, displays all node permissions granted to administrator. The query is sorted by name, then module, then permission, all ascending.

Figure 9.10. A query before altering

Notice that the tag, 'get_permissions' was added to this query. Tagging the query just marks the query with some descriptive tags and doesn't cause any immediate processing. However, it is possible to create a hook that will be invoked when a tagged query is encountered.

You can put any tag on your query but there are some conventions used in the core modules:

All dynamic queries are passed through the query alter hook when the execute() method is called and just before the query string is compiled. That means modules have the opportunity to alter the query before it goes to the database.

The format for the hook is module_name_query_tag_name_alter, where module_name is the name of our module, and tag_name is the name of the tag we added. The hook function that captures the query is shown in Listing 9-23.

function weather_info_query_get_permissions_alter(QueryAlterableInterface $query) {
  $order =& $query->getOrderBy();
  $order['permission'] = 'DESC';
}

This function will modify the ordering parameters, changing the order of 'permission' from 'ASC' to 'DESC'. Notice that the query is passed as a type of QueryAlterableInterface. Even though it is not passed by reference, it acts as though it is because of the way PHP 5 handles objects. It is essential to grab the properties you are looking for using the reference assignment (=&) so that anything that's altered affects the original query string.

Table 9-1 shows the properties that are available to the query alter hook.

There is no need to take any action inside the query alter hook. As soon as the function is completed, the query engine will look for any other alter hooks that need to be executed, and then it will compile the query string and send it to the database engine.

The result of our query, having been created in our module and altered to change the sort order, is shown in Figure 9-11.

Figure 9.11. The result of an altered query

In sum, the dynamic query model provides all of the functionality of the static query model, with the added benefit of being cross-database compatible. I believe it's also easier to read and maintain due to its self-documenting mode of expression.

Many of the methods that are available for SELECT queries are also available for other queries, as we will see in the next sections.

The db_insert() function can be executed with more than one record being inserted at a time. To do this, you must use a "degenerate" form of the query. Listing 9-27 shows the form using the query we've been working with.

$hid = db_insert('weather_info')
  ->fields(array(
      'location',
      'language',
      'units',
      'uid',
      'timestamp'))
  ->values(array(
      'location' => 'San Luis Obispo',
      'language' => 'en',
      'units' => 'R',
      'uid' => 1,
      'timestamp' => REQUEST_TIME,
      ))
  ->execute();

Notice that the fields and values are specified separately. By using this form, we can specify multiple sets of values and then execute them all at once, as shown in Listing 9-28.

$hid = db_insert('weather_info')
  ->fields(array(
      'location',
      'language',
      'units',
'uid',
      'timestamp'
      ))
  ->values(array(

'San Luis Obispo',
      'en',
      'R',
      1,
      REQUEST_TIME,
      ))
  ->values(array(
      'Cupertino',
      'en',
      'F',
      1,
      REQUEST_TIME,
      ))
  ->values(array(
      'San Diego',
      'es',
      'C',
      1,
      REQUEST_TIME,
      ))
  ->execute();

Another way to execute this multi-record insert would be to create an array and then iterate over it using the foreach() control structure, as shown in Listing 9-29.

$values = array(
    array('San Luis Obispo', 'en', 'R', 1, REQUEST_TIME),
    array('Cupertino', 'en', 'F', 1, REQUEST_TIME),
    array('San Diego', 'es', 'C', 1, REQUEST_TIME),
    );

  $query = db_insert('weather_info')
    ->fields(array(
       'location',
       'language',
       'units',
       'uid',
       'timestamp'
       ));
  foreach($values as $record) {
    $query->values($record);
  }
  $hid = $query->execute();
  print $hid;

This method is used often in the core as well as in many contributed modules, so you should feel comfortable with it. Note that if you are using the multi-insert technique, the return value is undefined and should not be trusted as it may vary depending on the database driver.

Not only is this method a bit more straightforward than executing three separate inserts, it is potentially safer and more efficient. It is up to the database driver to determine the best way to do this insert. For example, multiple insert statements could be executed together inside a transaction for greater data integrity and speed. Or, if the database engine supports batch inserts, the driver might use that approach instead.

Expressions can be used to do some computation when a record is inserted. An example is shown in Listing 9-34.

db_merge('search_index')
  ->key(array(
    'word' => $word,
    'sid' => $sid,
    'type' => $module,
  ))
  ->fields(array('score' => $score))
  ->expression('score', 'score + :score', array(':score' => $score))
  ->execute();

This is commonly used where there is some sort of counter that keeps track of similar records in the table. The example in Listing 9-34 is used by the search function. In this case, if the word doesn't exist in the search_index table, it is added with the precalculated score value. If it is already there, the score value is incremented by the score value.

Conditions can also be nested by specifying multiple condition() properties. This is shown in Listing 9-36.

$query
  ->condition('field1', array(1, 2), 'IN')
  ->condition(db_or()->condition('field2', 5)->condition('field3', 6));
// Becomes: (field1 IN (1, 2) AND (field2 = 5 OR field3 = 6))

The default when specifying multiple conditions is to "AND" them together. We see this with the two conditions that are on separate lines. The db_or() function also takes multiple condition() properties but returns a SQL string set to "OR" all included conditions together. Combining that with the first condition gives us the desired conditional string.

There are two methods for specifying the "nullness" of a field in the query, as shown in Listing 9-37.

$query->isNull('language'),
// Becomes (language IS NULL)

$query->isNotNull('language'),
// Becomes: (language IS NOT NULL)