Building queries using Database API

submitted by aragorn

One of the major advantages of Drupal 7 over its precursors is its flexibility to settings and systems. Its developers haven't bypassed DB queries. In this release they have become objective-oriented and not attached to some particular database, since Drupal itself now generates query syntax. Programmer's task is just to indicate conditions of selection. This article is dedicated to Database API analysis where I'll give examples of how "communication" with DB is changed.

First of all I'd like to mention that syntax of site connection to base is changed. From usual

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

to more readable

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

It's worth mentioning also that Drupal 7 still supports db_query(), but it was slightly changed - namely, placeholders were changed. One of the alternatives is usage of ? symbol instead of %d, '%s'. In this case values of such placeholders are indicated in array after 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 usage of so called "nominal" placeholders. Value replacement happens in associative array. One and the same placeholder cannot be described twice, even if it has the same value. It's appropraite to call them by 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. Earlier we used db_query() and we specified necessary type of query inside (select, insert, update, delete), now each of them has corresponding function (db_select(), db_insert(), db_update, db_delete()), on the basis of which necessary syntax is generated. This is the list of acceptable functions:

 

  • db_and
  • db_close
  • db_condition
  • db_delete
  • db_driver
  • db_escape_field
  • db_escape_table
  • db_insert
  • db_like
  • db_merge
  • db_next_id
  • db_or
  • db_query
  • db_query_range
  • db_query_temporary
  • db_select
  • db_set_active
  • db_transaction
  • db_truncate
  • db_update
  • db_xor

Definition of format of data that were received during call to database was also altered. To replace such functions as db_result(), db_fetch_object() and others, the methods of DatabaseStatementInterface came up:

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

Indispensable condition of query execution is presence of execute(). It must be indicated before format definition.

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 from which selection will  be done. Then we add method fields() in which table and fields for selection are indicated. If fields are not indicated, query will return all fields of the table (analogue *). Execute() provides execution of our query, and fetchAssoc() will return a result as 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 identidied 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 are also other methods of query that allow to join tables (join), group values (groupBy), make their order (orderBy), limit number of results (range) and others. All of them, when combined, allow to build complicated queries to DB.

Let's show an example of creating query for selection of values of several tables, using grouping and sorting, limiting 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);
}
?>

All queries work like that. To prove these words, we'll give the examples of using queries to insert and delete 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 high speed of processing, better readibility and possibility to make changes, it also builds syntax of query, decreasing the chance to make a mistake in structure and is not attached to one database, which guarantees the possibility of query execution for different databases.

You can get to know the comparison of building queries for Drupal 6 and 7 using presentation. See here.