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 ;)

Credo Systemz (not verified)

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

Casimira (not verified)

canadian online pharmacy viagra

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

viagra from india

Vivek Agarwal (not verified)

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

Shivam (not verified)

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

Ellemox (not verified)

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

GregoryGem (not verified)

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

Williamnob (not verified)

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

MitchelFum (not verified)

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

StevVise (not verified)

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

StevVise (not verified)

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

StevVise (not verified)

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

Hortense (not verified)
Isidra (not verified)

is there a generic viagra

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

cheap viagra online

StevVise (not verified)

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

JosephTug (not verified)

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

JosephTug (not verified)

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

JosephTug (not verified)

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

Bryanensg (not verified)

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

JosephTug (not verified)

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

RobertTiend (not verified)

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

Jordanenyf (not verified)

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

Jordanenyf (not verified)

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

Jordanenyf (not verified)

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

RobertTiend (not verified)

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

Patrickenyv (not verified)

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

RobertTiend (not verified)

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

RobertTiend (not verified)

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

RobertTiend (not verified)

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

RobertTiend (not verified)

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

Lawrenceentw (not verified)

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

Lawrenceentw (not verified)

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

RobertTiend (not verified)

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

Curtisenkh (not verified)

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

Curtisenkh (not verified)

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

Curtisenkh (not verified)

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

Aaronbriek (not verified)

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

Debora (not verified)

how much does viagra cost

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

viagra discount

Douglasenxc (not verified)

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

Aaronbriek (not verified)

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

Eleanorenhr (not verified)

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

Aaronbriek (not verified)

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

Audreyenfa (not verified)

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

Audreyenfa (not verified)

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

Aaronbriek (not verified)

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

Andrewenmq (not verified)

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

Aaronbriek (not verified)

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

Aaronbriek (not verified)

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

Add new comment