How to work with database in Drupal 7

Posted by admin on Thu, 02/09/2017 - 12:57

All common Drupal 7 database examples here: select, update, delete, insert, join, filter, sorting, limit etc.

SELECT

SELECT with condition

// Drupal 6 version
$nodes = db_query("
SELECT nid, title FROM {node}
  WHERE type = '%s' AND uid = %d
", 'page', 1);
 
// Drupal 7, static query
$nodes = db_query("
  SELECT nid, title FROM {node}
  WHERE type = :type AND uid = :uid
", array(':type' => 'page', ':uid' => 1))->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->condition('n.type', 'page')
  ->condition('n.uid', 1)
  ->execute()
  ->fetchAll();

SELECT from 2 database tables using INNER JOIN

// Drupal 6 version
$nodes = db_query("
  SELECT n.title, u.name FROM {node} n
  INNER JOIN {users} u ON n.uid = u.uid
");
 
// Drupal 7, static query
$nodes = db_query("
  SELECT n.title, u.name FROM {node} n
  INNER JOIN {users} u ON n.uid = u.uid
")->fetchAll();
 
// Drupal 7, dynamic query
$query = db_select('node', 'n');
$query->innerJoin('users', 'u', 'n.uid = u.uid');
$query->fields('n', array('title'));
$query->fields('u', array('name'));
$nodes = $query->execute()->fetchAll();

Please note, when using some database methods like JOIN - the query building should be separated with a semicolon!

Using db_select()->method1()->innerJoin()->method2() will cause an error!

Getting a single value from query

// Drupal 6 version
$title = db_result(db_query("SELECT title FROM {node} WHERE nid = %d", 123));
 
// Drupal 7, static query
$title = db_query("SELECT title FROM {node} WHERE nid = :nid", array(':nid' => 123))->fetchField();
 
// Drupal 7, dynamic query
$title = db_select('node', 'n')
  ->fields('n', array('title'))
  ->condition('n.nid', 123)
  ->execute()
  ->fetchField();

Getting the object by its ID

// Drupal 6 version
$node = db_fetch_object(db_query("SELECT * FROM {node} WHERE nid = %d", 123));
 
// Drupal 7, static query
$node = db_query("SELECT * FROM {node} WHERE nid = :nid", array(':nid' => 123))->fetchObject();
 
// Drupal 7, dynamic query
$node = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', 123)
  ->execute()
  ->fetchObject();

Count the number of entries

// Drupal 6
$count = db_result(db_query("SELECT COUNT(*) FROM {node} n WHERE n.uid = 1"));
 
// Drupal 7, static query
$count = db_query("SELECT COUNT(*) FROM {node} n WHERE n.uid = 1")->fetchField();
 
// Drupal 7, dynamic query, example 1
$count = db_select('node', 'n')
  ->condition('n.uid', 1)
  ->countQuery()
  ->execute()
  ->fetchField();
 
// Drupal 7, dynamic query, example 2
$query = db_select('node');
$query->addExpression('COUNT(*)');
$count = $query->execute()->fetchField();

Fround the minimum value

// Drupal 6 version
$min = db_result(db_query("SELECT MIN(fieldname) FROM {table}"));
 
// Drupal 7, static query
$min = db_query("SELECT MIN(fieldname) FROM {table}")->fetchField();
 
// Drupal 7, dynamic query
$query = db_select('table');
$query->addExpression('MIN(fieldname)');
$min = $query->execute()->fetchField();

Using LIMIT

// Drupal 6 version
$nodes = db_query("SELECT * FROM {node} LIMIT 0, 10");
 
// Drupal 7, static query
$nodes = db_query("SELECT * FROM {node} LIMIT 0, 10")->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n')
  ->range(0, 10)
  ->execute()
  ->fetchAll();

Run around all entries

// Drupal 6 version
$nodes = db_query("SELECT * FROM {node}");
while ($node = db_fetch_object($nodes)) {
  $items[] = $node->title;
}
 
// Drupal 7, static query
$result = db_query("SELECT * FROM {node}");
foreach ($result as $node) {
  $items[] = $node->title;
}
 
// Drupal 7, dynamic query
$result = db_select('node', 'n')->fields('n')->execute();
foreach ($result as $node) {
  $items[] = $node->title;
}

UPDATE

Update entry

// Drupal 6
db_query("UPDATE {node} SET status = %d WHERE nid = %d", 1, 123);
 
// Drupal 7, static query
db_query("UPDATE {node} SET status = :status WHERE nid = :nid", array(':status' => 1, ':nid' => 123));
 
// Drupal 7, dynamic query
db_update('node')
  ->fields(array('status' => 1))
  ->condition('nid', 123)
  ->execute();

Increment value

// Drupal 6
db_query("UPDATE {node_counter} SET totalcount = totalcount + 1 WHERE nid = %d", 123);
 
// Drupal 7, static query
db_query("UPDATE {node_counter} SET totalcount = totalcount + 1 WHERE nid = :nid", array(':nid' => 5));
 
// Drupal 7, dynamic query
db_update('node_counter')
  ->expression('totalcount', 'totalcount + 1')
  ->condition('nid', 5)
  ->execute();

REMOVE entry

// Drupal 6
db_query("DELETE FROM {node} WHERE uid = %d AND created < %d", 1, time() - 3600);
 
// Drupal 7, static query
db_query("DELETE FROM {node} WHERE uid = :uid AND created < :created", array(':uid' => 1, ':created' => time() - 3600));
 
// Drupal 7, dynamic query
db_delete('node')
  ->condition('uid', 1)
  ->condition('created', time() - 3600, '<')
  ->execute();

TRUNCATE (clear) table

// Drupal 6, Drupal 7 static query
db_query("TRUNCATE {node}");
 
// Drupal 7, dynamic query
db_truncate('node')->execute();

INSERT

// Drupal 6
db_query("INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (%d, '%s', %f)", 5, 'yum yum', 3.14);
$id = db_last_insert_id();
 
// Drupal 7, dynamic query
$id = db_insert('mytable')
  ->fields(array(
    'intvar' => 5,
    'stringvar' => 'yum yum',
    'floatvar' => 3.14,
  ))
  ->execute();

SPECIFIC CONDITIONS

Using LIKE operator in conditions

// Drupal 6
$nodes = db_query("SELECT * FROM {node} WHERE title LIKE '%%%s%%'", 'substring');
 
// Drupal 7, static query
$nodes = db_query("SELECT * FROM {node} WHERE title LIKE :title", array(':title' => '%' . db_like('substring') . '%'))->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.title', '%' . db_like('substring') . '%', 'LIKE')
  ->execute()
  ->fetchAll();

Using BETWEEN in conditions

// Drupal 6
$nodes = db_query("SELECT * FROM {node} WHERE nid BETWEEN %d AND %d", 123, 456);
 
// Drupal 7, static query
$nodes = db_query("SELECT * FROM {node} WHERE nid BETWEEN :nid1 AND :nid2", array(':nid1' => 123, ':nid2' => 456))->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', array(123, 456), 'BETWEEN')
  ->execute()
  ->fetchAll();

Check NULL or not

// Drupal 6, Drupal 7 static query
$result = db_query("SELECT * FROM {table} WHERE field IS NULL");
 
// Drupal 7, dynamic query, вариант 1
$result = db_select('table', 't')
  ->fields('t')
  ->condition('t.field', NULL, 'IS NULL')
  ->execute();
 
// Drupal 7, dynamic query, вариант 2
$result = db_select('table', 't')
  ->fields('t')
  ->isNull('t.field')
  ->execute();

Using coplicated things in WHERE clause

// Drupal 6
$nodes = db_query("SELECT * FROM {node} WHERE YEAR(FROM_UNIXTIME(created)) = %d", 2011);
 
// Drupal 7, static query
$nodes = db_query("SELECT * FROM {node} WHERE YEAR(FROM_UNIXTIME(created)) = :created", array(':created' => 2011))->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n')
  ->where('YEAR(FROM_UNIXTIME(n.created)) = :created', array(':created' => 2011))
  ->execute()
  ->fetchAll();

SORTING

// Drupal 6
$nodes = db_query("SELECT * FROM {node} ORDER BY created DESC, title ASC");
 
// Drupal 7, static query
$nodes = db_query("SELECT * FROM {node} ORDER BY created DESC, title ASC")->fetchAll();
 
// Drupal 7, dynamic query
$nodes = db_select('node', 'n')
  ->fields('n')
  ->orderBy('n.created', 'DESC')
  ->orderBy('n.title', 'ASC')
  ->execute()
  ->fetchAll();

GETTING RESULTS in specific way

Get results into an associative array

// Drupal 6
$result = db_query("SELECT nid, title, created FROM {node}");
$nodes = array();
while ($row = db_fetch_object($result)) {
  $nodes[$row->nid] = $row;
}
 
// Drupal 7, static query
$nids = db_query("SELECT nid, title, created FROM {node}")->fetchAllAssoc('nid');
 
// Drupal 7, dynamic query
$nids = db_select('node', 'n')
  ->fields('n', array('nid', 'title', 'created'))
  ->execute()
  ->fetchAllAssoc('nid');

Get results into an assicative array (where keys - first colum, values - second)

// Drupal 6
$result = db_query("SELECT nid, title FROM {node}");
$titles = array();
while ($row = db_fetch_object($result)) {
  $nids[$row->nid] = $row->title;
}
 
// Drupal 7, static query
$titles = db_query("SELECT nid, title FROM {node}")->fetchAllKeyed();
 
// Drupal 7, dynamic query
$titles = db_select('node', 'n')
  ->fields('n', array('nid', 'title'))
  ->execute()
  ->fetchAllKeyed();
×

Hey! Don't miss a new cool stuff!

Comments

Tester (not verified)

It works, yeah?

admin

Be sure it is ;)

nvzuosh (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

nvzuosh (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

yyoqnvs (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

yyoqnvs (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

nousmhl (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

nousmhl (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

nzkloya (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

qvqbipw (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

qvqbipw (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

Credo Systemz (not verified)

Wow nice and excellent article... all information explanation and program examples are very clear so easy to understand the concepts

Vivek Agarwal (not verified)

Nice and to the point article.. great....

Shivam (not verified)

It's very very nice and useful. Easy to understand

Jamesemame (not verified)

Hi there! cialis vs viagra good website.

BobbySet (not verified)

free sex cam sites adultfreewebcamsites.com top cam sites.

BobbySet (not verified)

live cam girls camgirls1.com korean cam girls.

DanielLox (not verified)

webcam sex show hot sexy cam free couple live sex cam.

DanielLox (not verified)

cheap cam sites porn cam sites popular webcam sites.

Jamesemame (not verified)

Hello! erectile dysfunction great internet site.

Jamesemame (not verified)

Hello! erectile dysfunction great internet site.

Jamesemame (not verified)

Hello! erectile dysfunction great internet site.

Jamesemame (not verified)

Hello! erectile dysfunction great internet site.

ArthurNog (not verified)

Howdy! buy clomid pct uk very good web site.

ArthurNog (not verified)

Howdy! buy clomid pct uk very good web site.

ArthurNog (not verified)

Howdy! buy clomid pct uk very good web site.

ThomasJes (not verified)

Hi! dapoxetine very good website.

Jamesemame (not verified)

Hello! how does cialis work great web page.

Jamesemame (not verified)

Hello! how does cialis work great web page.

isbtgmz (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

isbtgmz (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

Desmond (not verified)

дом 2 ютуб

сайт дома два

девушки дом 2

tafdzxe (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

Cecile (not verified)

Смотреть фильм онлайн Последний бросок онлайн посмотреть в хорошем качестве предлагаем

смотреть фильмы и сериалы в хорошем качестве

ArthurNog (not verified)

Howdy! buy clomid fertility drug excellent site.

ArthurNog (not verified)

Howdy! buy clomid fertility drug excellent site.

bhcjosc (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

lszxjcd (not verified)

Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн

ThomasJes (not verified)

Howdy! where buy priligy beneficial web page.

Joesph (not verified)

лайф дом 2

дом 2 сайт

дом 2 серии онлайн

ThomasJes (not verified)

Howdy! where buy priligy beneficial web page.

ThomasJes (not verified)

Howdy! where buy priligy beneficial web page.

Clinton (not verified)

дом 2 смотреть

дома 2 онлайн

смотреть эфиры дом 2

Jamesemame (not verified)

Hello there! ed medications excellent site.

Jamesemame (not verified)

Hello there! ed medications excellent site.

ArthurNog (not verified)

Hello there! buy clomid bodybuilding great internet site.

ShawnHom (not verified)

cam 2 cam sex free webcam sex love sex cam.

LarryEnusy (not verified)

free sex cam sites adultfreewebcamsites naked webcam sites.

ThomasJes (not verified)

Howdy! where can i buy colchicine in the uk excellent site.

ThomasJes (not verified)

Howdy! where can i buy colchicine in the uk excellent site.