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

Vivek Agarwal (not verified)

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

Shivam (not verified)

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

Bulk SMS USA (not verified)

Real time communication has become a big thing for consumers looking to purchase different products or use a new service. Using Bulk SMS USA, businesses can engage with these consumers letting them know of their latest promotions and services. Plus, it is a great way to improve user experience to build loyalty.

Assignment Writ... (not verified)

Get best assignment writing services in Australia, UK, and the USA from EssayCorp. We provide reliable assignment and essay writing services at affordable prices.
Assignment Writing Services
Assignment help australia
Assignment help in Australia

Laboratory Equi... (not verified)

Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. Laboratory Equipments

Assignment Help (not verified)

Get best assignment writing services in Australia, UK, and the USA. We provide reliable assignment and essay writing services at affordable prices.
Computer Science Assignment in Australia
finance assignment in australia
Accounting Assignment in Australia

gowsalya (not verified)

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
msbi online training

sasireka (not verified)

Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information. 

ReactJS Online Training

priya (not verified)

Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.

Tableau online training

jothikumar (not verified)

This is a very good content you share on this blog. it's very informative and provide me future-related information. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Sql server dba online training

kiruthiprabha (not verified)

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this..
Oracle DBA Online Training

Anonymous (not verified)

Fantastic work! This is the type of information that should follow collective approximately the web. Embarrassment captivating position Google for not positioning this transmit higher! Enlarge taking place greater than and visit my web situate
https://www.gangboard.com/app-programming-scripting-training/core-java-t...

Anonymous (not verified)

Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.

AWS online training

jothiaaron (not verified)

Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your ideas.
Docker online training

karthik (not verified)

This is such a good post. One of the best posts that I\'ve read in my whole life. I am so happy that you chose this day to give me this. Please, continue to give me such valuable posts. Cheers!
Advanced Java online training

jothi (not verified)

Thanks for the good words! Really appreciated. Great post. I’ve been commenting a lot on a few blogs recently, but I hadn’t thought about my approach until you brought it up.
Selenium training in Chennai | Devops online training

jayapriya (not verified)

It would have been the happiest moment for you,I mean if we have been waiting for something to happen and when it happens we forgot all hardwork and wait for getting that happened.
Java training in chennai

Add new comment