Building queries using Database API

One of Drupal 7's major advantages over its precursors is its flexibility with settings and systems. Drupal developers haven't bypassed DB queries, too. In this release the latter have become objective-oriented and are no more attached to some particular database, for the query syntax is now generated by Drupal itself. Thus, the programmer's task now is simply to indicate the required conditions for the selection. This article deals with Database API analysis, which will result in the examples of the changes that occurred to the way the "communication" with DB is performed now.

First of all I'd like to point out some changes that the syntax of site to database >connection has undergone. From the former reading (see below)

php $db_url = 'mysqli://username:password@localhost/basename';

it has transformed into a more readable version

php
$databases['default']['default'] = array( 
  'driver'   => 'mysql', 
  'database' => 'basename', 
  'username' => 'username', 
  'password' => 'password', 
  'host'     => 'localhost', 
);

It's also worth mentioning that Drupal 7 still keeps supporting db_query(), though some minor change having been made hereby, namely, the placeholders changed. One of the suggested options being the use of ? Symbol instead of %d, '%s' , in which case the values of such placeholders are indicated in the array after the query in the same order as they are placed in the query:

php
db_query("SELECT `title` FROM {node} WHERE `uid` = ? AND `type` = ?", array(1, 'page'));

The second option is the one which deals with the usage of the so-called "nominal" placeholders. The value replacement is now performed in the associative array. One and the same placeholder cannot be described twice, nor even if it has the same value. Now, It's an appropriate practice to have their names assigned the same as of the corresponding table columns whose value they define:

 php
db_query("SELECT `title` FROM {node} WHERE `uid`= :uid AND `type` = :type", array(':uid' => 1, ':type' => 'page'));
?>

But let's return to our subject and look at the novelties in this sphere. While previously we used db_query() with the necessary type specified within (select, insert, update, delete), now each of them has the corresponding function (db_select(), db_insert(), db_update, db_delete()), and that supplies sufficient basis for the necessary syntax to be generated. Below is the list of the acceptable functions:

execute - executes a prepared statement;

1. fetchAllAssoc - returns the result set as an associate array keyed by the given field;
2. fetchAllKeyed - returns the entire result set as a single associative array;
3. fetchAssoc - fetches the next row and returns it as an associative array;
4. fetchCol - returns an entire single column of a result set as an indexed array;
5. fetchField - returns a single field from the next record of a result set;
6. getQueryString - gets the query string of this statement;
7. rowCount - returns the number of rows affected by the last SQL statement.

execute() is a substantial condition for the query execution. It should be indicated prior to defining the format.

Now let's see how it works. We'll create a query that will return the content of "node" table:

php
$result = db_select('node', 'n') 
  ->fields('n') 
  ->execute() 
  ->fetchAssoc();

In db_select it's necessary to indicate a table (and, if necessary, its abbreviation as well) from which the selection will be done. Then we add method fields() in which the table and fields for selection are indicated. If fields are not indicated, the query will return all fields of the table (analogue *). Execute() provides execution of our query, and fetchAssoc() will return a result as an associate array. Specifying necessary fields, it's necessary to consider one peculiarity - they must be listed in array, even if only one field is indicated!

If some condition must be indicated in our query, we need to use conditions() method for that. It accepts three values - field, field value, comparison parameter. If the last one is not mentioned, it'll be identified as "-" by default:

php
$result = db_select('node', 'n') 
  ->fields('n', array('type', 'tittle')) 
  ->condition('nid', $node->nid, '=') 
  ->condition('status', 0, '>') 
  ->condition('uid', array(1, 5, 7), 'IN') 
  ->execute() 
  ->fetchAssoc();

There also exist some other query methods of that allow for joining tables (join), group9ing values (groupBy), their sorting (orderBy) and limiting the number of results (range) and others. All of them, when combined, facilitate making complicated DB queries.

Let's see some example of how the query for selecting the values out of several tables, as done with use of grouping and sorting, and limiting the number of results:

php
$query = db_select('node', 'n'); 
$query->join('users', 'u', 'n.uid = u.uid'); 
$query->groupBy('u.uid'); 
$query->fields('n', array('title', 'created')) 
  ->fields('u', array('name')) 
  ->orderBy('created', 'DESC') 
  ->range(0, 10); 
$result = $query->execute(); 
while ($record = $result->fetchAssoc()) { 
   print_r ($record); 
}

It's a standard practice for all queries. To prove it, we'll give examples of the queries as used for inserting and deleting entries.

php
$id = db_insert('node')->fields(array( 
  'title'        => 'Title', 
  'uid'         => 1, 
  'created' => REQUEST_TIME, 
))->execute();

 php
db_delete('node') 
  ->condition('uid', 5) 
  ->condition('created', time() - 3600, '<') 
  ->execute();

Database API, embodied in Drupal 7, allows to build voluminous queries, it has increased the speed of their processing, improved readability and is more open to changes. It also builds the query syntax on its own and that decreases the possibility of user mistake in the structure, and is no more attached to one and only database, which secures that the query be executed for different databases.

You can compare the types of queries as built for Drupal 6 and 7 via the presentation, See here.