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();
Tags: 
drupal
drupal 7
database
×

Hey! Don't miss a new cool stuff!

Comments

02/09/2017 - 14:31
Tester (not verified)

It works, yeah?

03/09/2017 - 08:29
admin

Be sure it is ;)

05/30/2017 - 05:16
Credo Systemz (not verified)

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

02/23/2020 - 02:49
Casimira (not verified)

canadian online pharmacy viagra

Скажене весілля. Скажене весілля 2

viagra from india

02/14/2018 - 05:30
Vivek Agarwal (not verified)

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

08/24/2018 - 16:37
Shivam (not verified)

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

01/11/2020 - 12:11
Ellemox (not verified)

Comprare Levitra In Italia Buy Amoxicillin Amazon 252 Mtabs Scam Cialis Cytotec En Ligne

01/21/2020 - 23:56
Eugenehen (not verified)

wiki online dating sites
gedpcourcutua.tk
termacongdertme.ml
teen daughter walks in on dad waching porn
stilgitempge.tk

01/23/2020 - 09:26
GregoryGem (not verified)

[url=https://wratotadaper.ml]wratotadaper.ml[/url]

01/23/2020 - 09:48
Williamnob (not verified)

[url=https://wratotadaper.ml]wratotadaper.ml[/url]

01/23/2020 - 09:53
MitchelFum (not verified)

[url=https://wratotadaper.ml]wratotadaper.ml[/url]

02/04/2020 - 17:58
StevVise (not verified)

Zithromax Sinusitis Dosage [url=http://buyciaonlinex.com/#]buy generic cialis online[/url] Provera In Internet Discount Overseas Cialis Tadarise

02/09/2020 - 20:06
StevVise (not verified)

Acquistare Kamagra Online Di Quebec [url=http://buyciallisonline.com/#]viagra vs cialis[/url] Buy Online Levitra Cialis Keflex Cystitis

02/09/2020 - 20:09
StevVise (not verified)

Acquistare Kamagra Online Di Quebec [url=http://buyciallisonline.com/#]viagra vs cialis[/url] Buy Online Levitra Cialis Keflex Cystitis

02/22/2020 - 23:03
Hortense (not verified)

viagra cost

Скажене весілля. Скажене весілля 2

viagra pills

02/23/2020 - 03:14
Isidra (not verified)

is there a generic viagra

Скажене весілля. Скажене весілля 2

cheap viagra online

02/09/2020 - 20:10
StevVise (not verified)

Acquistare Kamagra Online Di Quebec [url=http://buyciallisonline.com/#]viagra vs cialis[/url] Buy Online Levitra Cialis Keflex Cystitis

02/10/2020 - 23:09
JosephTug (not verified)

Hello! [url=http://aciclovir.ru.com/]buy aciclovir[/url] buy aciclovir tablets online buy generic aciclovir tablets in uk

02/10/2020 - 23:10
JosephTug (not verified)

Hello! [url=http://aciclovir.ru.com/]buy aciclovir[/url] buy aciclovir tablets online buy generic aciclovir tablets in uk

02/10/2020 - 23:44
JosephTug (not verified)

Hi! [url=http://aciclovir.ru.com/]buy generic aciclovir tablets in uk[/url] purchase Aciclovir zovirax 200mg tablets

02/11/2020 - 06:07
Bryanensg (not verified)

Howdy! [url=http://aciclovir.ru.com/]http://aciclovir.ru.com[/url] - zovirax online beneficial web site

02/11/2020 - 12:35
JosephTug (not verified)

Hello there! [url=http://aciclovir.ru.com/]zovirax[/url] buy aciclovir canada non prescribed zovirax tablets

02/11/2020 - 23:23
RobertTiend (not verified)

Hello! [url=http://dapoxetine4.com/]buy dapoxetine no prescription[/url] buy dapoxetine pills generic priligy

02/12/2020 - 04:23
Jordanenyf (not verified)

Hello there! [url=http://dapoxetine4.com/]http://dapoxetine4.com[/url] - buy priligy excellent web site

02/12/2020 - 04:24
Jordanenyf (not verified)

Hello there! [url=http://dapoxetine4.com/]http://dapoxetine4.com[/url] - buy priligy excellent web site

02/12/2020 - 04:28
Jordanenyf (not verified)

Hello there! [url=http://dapoxetine4.com/]http://dapoxetine4.com[/url] - buy priligy excellent web site

02/12/2020 - 09:20
RobertTiend (not verified)

Hi! [url=http://dapoxetine4.com/]purchase priligy online no prescription[/url] buy priligy pills online buy priligy with no prescription

02/12/2020 - 14:22
Patrickenyv (not verified)

Howdy! [url=http://dapoxetine4.com/]http://dapoxetine4.com[/url] - priligy cheap beneficial internet site

02/12/2020 - 19:23
RobertTiend (not verified)

Hello there! [url=http://dapoxetine4.com/]buy priligy online without prescription[/url] buy priligy online without prescription buy priligy no rx

02/12/2020 - 21:07
RobertTiend (not verified)

Hello! [url=http://edpillstore.space/]buy generic ed pills[/url] buy ed pills no prescription ed pills online

02/12/2020 - 21:10
RobertTiend (not verified)

Hello! [url=http://edpillstore.space/]buy generic ed pills[/url] buy ed pills no prescription ed pills online

02/12/2020 - 21:13
RobertTiend (not verified)

Hello! [url=http://edpillstore.space/]buy generic ed pills[/url] buy ed pills no prescription ed pills online

02/13/2020 - 01:51
Lawrenceentw (not verified)

Hello there! [url=http://edpillstore.space/]http://edpillstore.space[/url] - buy ed pills pills great site

02/13/2020 - 01:52
Lawrenceentw (not verified)

Hello there! [url=http://edpillstore.space/]http://edpillstore.space[/url] - buy ed pills pills great site

02/13/2020 - 06:50
RobertTiend (not verified)

Hello there! [url=http://edpillstore.space/]ed pills online[/url] buy generic ed pills buy generic ed pills

02/13/2020 - 11:55
Curtisenkh (not verified)

Hello! [url=http://edpillstore.space/]http://edpillstore.space[/url] - buy ed pills no rx great internet site

02/13/2020 - 11:56
Curtisenkh (not verified)

Hello! [url=http://edpillstore.space/]http://edpillstore.space[/url] - buy ed pills no rx great internet site

02/13/2020 - 11:58
Curtisenkh (not verified)

Hello! [url=http://edpillstore.space/]http://edpillstore.space[/url] - buy ed pills no rx great internet site

02/13/2020 - 23:21
Aaronbriek (not verified)

Howdy! [url=http://finasteride-propecia.com/]propecia cheap[/url] finasteride 5mg finasteride 5mg

02/23/2020 - 03:01
Debora (not verified)

how much does viagra cost

Скажене весілля. Скажене весілля 2

viagra discount

02/14/2020 - 04:46
Douglasenxc (not verified)

Howdy! [url=http://finasteride-propecia.com/]http://finasteride-propecia.com[/url] - propecia online excellent web page

02/14/2020 - 10:24
Aaronbriek (not verified)

Howdy! [url=http://finasteride-propecia.com/]finasteride 5mg[/url] buy generic propecia buy finasteride

02/14/2020 - 16:07
Eleanorenhr (not verified)

Howdy! [url=http://finasteride-propecia.com/]http://finasteride-propecia.com[/url] - buy propecia cheap good site

02/14/2020 - 21:49
Aaronbriek (not verified)

Hello! [url=http://finasteride-propecia.com/]buy propecia online[/url] purchase finasteride propecia cheap

02/15/2020 - 03:25
Audreyenfa (not verified)

Hi there! [url=http://canadianonlinepharmacyhq.com/]http://canadianonlinepharmacyhq.com[/url] - cheap drug prices good web page

02/15/2020 - 03:27
Audreyenfa (not verified)

Hi there! [url=http://canadianonlinepharmacyhq.com/]http://canadianonlinepharmacyhq.com[/url] - cheap drug prices good web page

02/15/2020 - 09:28
Aaronbriek (not verified)

Hi there! [url=http://canadianonlinepharmacyhq.com/]online medical care[/url] prescription price checker top rated canadian pharmacies online

02/15/2020 - 15:39
Andrewenmq (not verified)

Hello there! [url=http://canadianonlinepharmacyhq.com/]http://canadianonlinepharmacyhq.com[/url] - buy prescription drugs without doctor very good web site

02/15/2020 - 21:21
Aaronbriek (not verified)

Howdy! [url=http://canadianonlinepharmacyhq.com/]canadian pharcharmy online[/url] overseas pharmacy prescription price checker

02/15/2020 - 21:22
Aaronbriek (not verified)

Howdy! [url=http://canadianonlinepharmacyhq.com/]canadian pharcharmy online[/url] overseas pharmacy prescription price checker

Add new comment