Drupal 7 DB abstraction layer

Simple queries

The most typical use case:

$result = db_query("SELECT * FROM {node} WHERE uid = :uid", array(':uid' => $uid));
foreach ($result as $record) {
  print $record->nid;
}

Get only one result for one field:

$nid = db_query("SELECT nid FROM {foo} WHERE uid = :uid", array(':uid' => $uid))->fetchField();

Get only one record:

$foo = db_query("SELECT * FROM {foo} WHERE uid = :uid", array(':uid' => $uid))->fetchArray();
$foo = db_query("SELECT * FROM {foo} WHERE uid = :uid", array(':uid' => $uid))->fetchObject();

More advanced queries

$query = db_select('civicrm_contribution', 'contrib')
             ->fields('contrib')
             ->condition('is_test', 0, '=');
$query->addJoin('left', 'civicrm_contact', 'contact', 'contact.id = contrib.contact_id');
$query->fields('contact', array('first_name', 'last_name', 'display_name', 'prefix_id', 'preferred_language', 'do_not_trade', 'contact_type'));
$result = $query->execute();
foreach ($result as $record) {
   // ...
}

Note that the "addJoin" has to be done separately, not with chaining, since it returns the table name, not the DB object.

To add a sum on a field, for example:

  $query->addExpression('sum(foo)', 'foo');

Debug

$query = db_select('node', 'n')
                   ->fields('node');
dsm((string) $query);

Tag: