Database API

02.04.2016

$nids = array(1, 2, 3);

// Static query
$result = db_query("
  SELECT n.nid, n.title FROM {node} n
  INNER JOIN {users} u ON n.uid = u.uid
  WHERE n.type = :type AND u.uid = :uid
  AND nid IN (:nids) AND title LIKE :title
  AND uid BETWEEN :uid1 AND :uid2
  AND YEAR(FROM_UNIXTIME(created)) = :created
  LIMIT 0, 10
  ORDER BY created DESC, title ASC
", array(':type' => 'page',
 ':uid' => 1,
 ':nids' => $nids,
 ':title' => '%' . db_like('substring') . '%',
 ':uid1' => 1, ':uid2' => 10,
 ':created' => 2000
 )));

// Dynamic query
$result = db_select('node', 'n');
$result->innerJoin('users', 'u', 'n.uid = u.uid');
$result->fields('n', array('nid', 'title'));
$result->condition('n.type', 'page');
$result->condition('u.uid', 1);
$result->condition('n.nid', $nids, 'IN')
$result->condition('n.title', '%' . db_like('substring') . '%', 'LIKE')
$result->condition('n.uid', array(1, 10), 'BETWEEN')
$result->isNull('n.title')
$result->where('YEAR(FROM_UNIXTIME(n.created)) = :created', array(':created' => 2011))
$result->range(0, 10)
$result->orderBy('n.title', 'ASC')
$result->execute();

обходим записи

foreach ($result as $record) {
  // Do something with each $record
  $node = node_load($record->nid);
}

или

$record = $result->fetch();              // По умолчанию.
$record = $result->fetchObject();        // Выборка как объект StdClass.
$record = $result->fetchAssoc();         // Ассоциативный массив.
$record = $result->fetchAllAssoc('nid'); // Выбрать в качестве ключа nid.

$record = $result->fetchField($column_index); // Выбрать одно поле из результатов.

Посчитать число записей

$number_of_rows = $result->rowCount(); //  для DELETE, INSERT или UPDATE
$number_of_rows = db_select('node')->countQuery()->execute()->fetchField(); // для SELECT

или

$number_of_rows = db_query("SELECT COUNT(*) FROM {node} n WHERE n.uid = 1")->fetchField();

Chaining

Добавляем запись

$result = db_query("INSERT INTO {customtable} (id, title, created) VALUES (%s, %d, %d)", 1, 'Example', time());

или

$id = db_insert('customtable')
  ->fields(array(
    'id' => 5,
    'title' => 'hello world',
  ))
  ->execute();

Обновляем запись

$num_updated = db_update('node')
  ->fields(array(
    'uid' => 5,
    'status' => 1,
  ))
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute();

db_query("UPDATE {node} SET status = :status WHERE uid = :uid", array(':status' => 1, ':uid' => 5));

Удаляем запись

db_query("DELETE FROM {node} WHERE nid = :nid", array(':nid' => 1));

или

db_delete('node')
  ->condition('nid', 1)
  ->execute();

Очищаем таблицу

db_query("TRUNCATE {customtable}");

или

db_truncate('customtable')->execute();

$nodes = db_query("SELECT * FROM {node} WHERE uid = :uid OR status = :status", array(':uid' => 1, ':status' => 0))->fetchAll();

или

$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition(
    db_or()
      ->condition('uid', 1)
      ->condition('status', 0)
  )
  ->execute()
  ->fetchAll();

#Drupal