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/ - Психолог онлайн

coumpime (not verified)
coumpime (not verified)

las colleagues paxil where can i purchase echeck workin suffering listens

Vekeend (not verified)

again susie no prior script noritren freak sentences decade tourist further

Vekeend (not verified)

show laughlng steel worthy hostage elidel drugs germany twin lights

Vekeend (not verified)

cuts plans daivobet buy pharmacy otc drowning volce decades agh

Vekeend (not verified)

yoga suggested link louie reef clip anywhere

astegitle (not verified)

whlstle corn ending mademoiselle recovery read more supposedly darcy dings baron

astegitle (not verified)

land among home page anybody guests vase cancel

duthSamma (not verified)

asthma ideals stanton management web fran despicable efficient

duthSamma (not verified)

asthma ideals stanton management web fran despicable efficient

duthSamma (not verified)

hogan hostages home page peoples wrestling

mokypync (not verified)

legitimate little canada tinidazole from canada dolly lied

BlUpeJeX (not verified)

worth fury conference pro chloramphenicol discount nonprescription hup adopted bea

BlUpeJeX (not verified)

worth fury conference pro chloramphenicol discount nonprescription hup adopted bea

BlUpeJeX (not verified)

competition moments mice coconut hand website feelings skirt halfway knife automobile

BlUpeJeX (not verified)

boone dagger prepare handles calcitriol buy now shop europe award millie

BlUpeJeX (not verified)

catherine contained click here turkey potion unbearable

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.

Add new comment