Drupal: Database Abstraction Layer

gistfile1.php

<?php

// SELECT One field: SELECT title FROM {node} WHERE nid = 123
$title = db_select('node', 'n')
  ->fields('n', array('title'))
  ->condition('n.nid', 123)
  ->execute()
  ->fetchField();
  
// SELECT One object: SELECT title FROM {node} WHERE nid = 123
$node = db_select('node', 'n')
  ->fields('n', array('title', 'body'))
  ->condition('n.nid', 123)
  ->execute()
  ->fetchObject();
            
// SELECT Many: SELECT n.nid, n.title FROM {node} n WHERE n.type = 'page'
$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->condition('n.type', 'page')
  ->execute();
            
foreach ($nodes as $node) {
  $items[] = $node->title;
}

// Show fields with aliases. 
$query = db_select('node', 'n');
$query->addField('node','nid','id');
$query->addField('node','title','name');
$query->execute();

// WHERE: SELECT * FROM {node} WHERE YEAR(FROM_UNIXTIME(created)) = 2011
$nodes = db_select('node', 'n')
  ->fields('n')
  ->where('YEAR(FROM_UNIXTIME(created)) = :created', array(':created' => 2011))
  ->execute();
            
// INSERT: INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (5, 'hello world', 3.14)
$record = array(
    'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14,
  );
drupal_write_record('mytable', $record);

db_insert('mytable')
  ->fields(array(
    'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14,
  ))
  ->execute();
            
// MULTI INSERT: INSERT INTO {mytable} (intvar, stringvar) VALUES (5, 'hello world'), (1, 'another world')
$query = db_insert('mytable')->fields(array('intvar', 'stringvar'));
  $query->values(array(
    'intvar' => 5,
    'stringvar' => 'hello world',
  ));
  $query->values(array(
    'intvar' => 1,
     'stringvar' => 'another world',
  ));
  $query->execute();
            
// UPDATE: UPDATE {mytable} SET stringvar = 'hi mate' WHERE intvar = 5
$record = array('stringvar' => 'hi mate');
drupal_write_record('mytable', $record, array('intvar'));

db_update('mytable')
  ->fields(array('stringvar' => 'hi mate'))
  ->condition('intvar', 5)
  ->execute();
            
// MERGE
db_merge('mytable')
  ->key(array('intvar' => 5))
  ->fields(array(
    'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14
  ))
  ->execute();
            
// DELETE: DELETE FROM {node} WHERE uid = 1
db_delete('node')
  ->condition('uid', 1)
  ->execute();
            
// DISTINCT: SELECT DISTINCT city FROM customers
$cities = db_select('customers', 'c')
  ->fields('n', array('city'))
  ->distinct()
  ->execute();

// JOIN: SELECT n.title, u.name FROM {node} n INNER JOIN {users} u ON n.uid = u.uid
$query = db_select('node', 'n');
  $query->innerJoin('users', 'u', 'n.uid = u.uid');
  $query->fields('n', array('title'));
  $query->fields('u', array('name'));
  $result = $query->execute();
            
// GROUP BY: SELECT n.title, n.type FROM {node} GROUP BY n.type
$count = db_select('node', 'n')
  ->fields('n', array('title', 'type'))
  ->groupBy('n.type')
  ->execute();
            
// COUNT: SELECT COUNT(*) FROM {node}
// 1st way:
$count = db_select('node')
  ->countQuery()
  ->execute()
  ->fetchField();
            
// 2nd way:
$query = db_select('node');
$query->addExpression('COUNT(*)');
$count = $query->execute()->fetchField();
            
// LIMIT: SELECT * FROM {node} LIMIT 0, 10
$result = db_select('node', 'n')
  ->fields('n')
  ->range(0, 10)
  ->execute();
            
// INCREMENT: UPDATE {node_counter} SET totalcount = totalcount + 1 WHERE nid = 123
db_update('node_counter')
  ->expression('totalcount', 'totalcount + 1')
  ->condition('nid', 123)
  ->execute();
            
// AND/OR: SELECT * FROM {node} WHERE uid = 1 OR status = 0
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition(
    db_or()
      ->condition('uid', 1)
      ->condition('status', 0)
  )
  ->execute();
            
// IN: SELECT * FROM {node} WHERE nid IN (1, 2, 3, 4)
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', array(1, 2, 3, 4), 'IN')
  ->execute();
            
// BETWEEN: SELECT * FROM {node} WHERE nid BETWEEN 123 AND 456
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', array(123, 456), 'BETWEEN')
  ->execute();
            
// LIKE: SELECT * FROM {node} WHERE title LIKE 'substring'
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.title', '%' . db_like('substring') . '%', 'LIKE')
  ->execute();
            
// IS NULL: SELECT * FROM {table} WHERE field IS NULL
// 1st way:
$result = db_select('table', 't')
  ->fields('t')
  ->condition('t.field', NULL, 'IS NULL')
  ->execute();
            
// 2nd way:
$result = db_select('table', 't')
  ->fields('t')
  ->isNull('t.field')
  ->execute();
            
// ORDER BY: SELECT * FROM {node} ORDER BY created DESC, title ASC
$nodes = db_select('node', 'n')
  ->fields('n')
  ->orderBy('n.created', 'DESC')
  ->orderBy('n.title', 'ASC')
  ->execute();
            
// Custom Formats:

// $nodes[$row->nid] = $row;
$nids = db_select('node', 'n')
  ->fields('n', array('nid', 'title', 'created'))
  ->execute()
  ->fetchAllAssoc('nid');
            
// $nids[] = $row->nid;
$nids = db_select('node', 'n')
  ->fields('n', array('nid'))
  ->execute()
  ->fetchCol();
            
// $nids[$row->nid] = $row->title;
$nids = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->execute()
  ->fetchAllKeyed();
            
// Add column to table.
db_add_field('table_name', 'colum_name', array('type' => 'text', 'size' => 'normal'));

/**
 * Transactions.
 */

// The transaction opens here.
$txn = db_transaction();

try {
  db_merge('mytable')
    ->key(array('intvar' => 5))
    ->fields(array(
      'intvar' => 5,
      'stringvar' => 'hello world',
      'floatvar' => 3.14,
  ))
  ->execute();
}
catch (Exception $e) {
  // Something went wrong somewhere, so roll back now.
  $txn->rollback();
  // Log the exception to watchdog.
  watchdog_exception('module_name', $e);
}