SQL query examples in Drupal 7

Regular query:

Source code viewer
// Loop through results.
$result = db_query('SELECT * FROM {some_table} WHERE nid = :nid', array(':nid' => $node->nid));
if ($result->rowCount() > 0)
  foreach ($result as $row)
    print_r($row);
// Get all results using chaining. More fetch possibilities under fetch section below.
$results = db_query('SELECT * FROM {some_table} WHERE nid = :nid AND title LIKE :title', array(':nid' => $nid, ':title' => $title))->fetchAll();
Programming Language: PHP

Database abstraction layer: Select

Source code viewer
// Iterate results through results.
$query = db_select('node', 'n')
  ->condition('n.nid', $node->nid)
  ->fields('n', array('type', 'title'))
  ->execute();
if ($query->rowCount() > 0) 
  while ($row = $query->fetchAssoc())
    dpm($row); // pretty print arrays, devel module
 
// Fetch all results.
$results = db_select('node', 'n')
  ->condition('n.nid', $node->nid)
  ->fields('n', array('type', 'title'))
  ->execute()
  ->fetchAll();
// Pretty print arrays, devel module.
dpm($results);
 
Programming Language: PHP

Source code viewer
// JOINS - Using joins, joins are not chainable.
$query = db_select('simplenews_subscription', 'o');
// Use leftJoin for left outer join.
$query->join('simplenews_subscriber', 'e', 'o.snid = e.snid')
  ->fields('o', array('tid', 'status'))
  ->fields('e', array('snid', 'activated'))
  ->condition('e.mail', $email)
  ->condition('o.tid', $tid);
 
// Limit / range - Fetch first result in this example. Usually used for performance reasons as well.
$query->range(0, 1);
 
// Remove duplicates - using distinct in Drupal db_select.
$query->distinct();
 
// Functions - You can use sql functions using addExpression ($expression, $alias = NULL, $arguments = array()).
$query->addExpression('MIN(o.tid)', 'min_term_id');
 
// In array condition (in, not in).
$query->condition('n.nid', array(1, 23, 43, 55), 'IN');
 
// Like condition (like, not like).
$query->condition('n.title', 'The%', 'LIKE');
 
// Regular expression as "where" condition.
$query->condition('u.name', '^info[0-9]+$', 'REGEXP');
 
// OR / AND conditions
$db_or = db_or();
 
$db_or->condition('o.created', 1);
$db_or->condition('o.created', 2);
 
$query->condition($db_or);
 
// Recieve the results - more fetch possibilities under fetch section below.
$results = $query
  ->execute()
  ->fetchAll();
// Pretty print results using devel module.
dpm($results);
Programming Language: PHP

Fetch functions:

Source code viewer
// Fetch next row as a stdClass object.
$result = $query->fetchObject();
 
// Fetch next row as an associative array.
$result = $query->fetchAssoc();
 
// Fetch next row from specific column (column number argument, defaults to 1).
$result = $query->fetchColumn($column_nr = 1);
 
// Fetch all records as indexed array of stdClass objects.
$result = $query->fetchAll();
 
// Fetch all records as accociative array of stdClass objects (column name of keys as argument).
$result = $query->fetchAllAssoc($key);
 
// Fetch all rows from specific column (column number argument, defaults to 1).
$result = $query->fetchCol($column_nr = 1);
 
// Fetch all results, some as keys (arguments specify which columns use as keys and which as values).
$result = $query->fetchAllKeyed($key_index = 0, $value_index = 1);
Programming Language: PHP

Load entity by field value:

Source code viewer
// Fetch a single entity.
$query = new EntityFieldQuery();
 
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'gallery')
  ->propertyCondition('status', 1)
  ->propertyCondition('uid', $uid)
  ->fieldCondition('field_pictures', 'value', $entity->item_id)
  ->fieldCondition('field_multivalue', 'value', $array, 'IN')
  ->range(0, 1);
 
$result = $query->execute();
if (isset($result['node'])) {
  $nids = array_keys($result['node']);
  $gallery = node_load($nids[0]);
  // Pretty print using devel module.
  dpm($gallery);
}
 
// Fetch multiple entities.
$nodes = new EntityFieldQuery();
$nodes
  ->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'model');
 
$nodes = $query->execute();
if (isset($nodes['node'])) {
  $node_ids = array_keys($nodes['node']);
  foreach ($node_ids as $nid) {
    dpm($nodes);
  }
}
Programming Language: PHP

EntityFieldQuery methods for Field Collection module:

Source code viewer
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'field_collection_item');
 
// No bundle value, use field name
$query->propertyCondition('field_name', 'field_appointment_document');
 
// I'm restricting to search items attached to something, so filtering by the FC I want to check.
$query->propertyCondition('item_id', $doc_fc_ids);
 
// We don't want revisions!
$query->propertyCondition('archived', 0);
 
// An example to filter by a field attached to the FC.
$query->fieldCondition('field_document_type', 'tid', array(123,432));
 
// No date information or useful other property info, but I want the newest one only.
// So highest ID is the newest, and only return one result.
$query->propertyOrderBy('item_id', 'DESC');
$query->range(0, 1);
Programming Language: PHP

Database abstraction layer: Insert

Source code viewer
db_insert('social_networks')->fields(array(
  'uid' => 1,
  'changed' => REQUEST_TIME,
  'data' => $data,
))
->execute();
Programming Language: PHP

Database abstraction layer: Update

Source code viewer
// Update, in this example we publish node which id is $node_id.
$count = db_update('node')
  ->fields(array(
    'status' => 1,
  ))
  ->condition('nid', $node_id)
  ->execute();
 
// Using mysql functions or custom code in db_update.
$count = db_update('field_data_body')
    ->expression('body_value', 'REPLACE(body_value, :find, :replace)', array(
      ':find' => $find, 
      ':replace' => $replace
    ))
    ->condition('body_value', '%' . $find . '%', 'LIKE')
    ->execute();
Programming Language: PHP

Database abstraction layer: Insert or update

Source code viewer
$record = array(
  'changed' => REQUEST_TIME,
  'data' => $data,
);
drupal_write_record('social_networks', $record);
 
$return = $record['uid'];
Programming Language: PHP

Database abstraction layer: Delete

Source code viewer
$num_deleted = db_delete('node')
  ->condition('nid', $nid)
  ->execute();
Programming Language: PHP