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

best place to b... (not verified)

Propecia Espanol

neurontin and codine (not verified)

Buy Online Cialis

best time of da... (not verified)

Levitra Ads

plaquenil and c... (not verified)

levitra prix medecine

overnight ciali... (not verified)

Secure Ordering Isotretinoin

cialis price (not verified)

Buy Zoloft Uk

gabapentin for dogs (not verified)

Buy Nexium Uk

neurontin uses (not verified)

Cialis 10 Mg Posologie

cialis professional (not verified)

comparateur prix cialis france

neurontin and codine (not verified)

Is Orlistat On 340b

buy cialis onli... (not verified)

cialis 10 mg patient directions

buy viagra s journal (not verified)

Dapoxetina Cuanto Cuesta

buy zithromax o... (not verified)

Viagra Mal Des Montagnes

how long does i... (not verified)

cialis free trial offer

finasteride 1 m... (not verified)

buy generic cialis online now

buy finasteride... (not verified)

Cialis Generika Online

LarryHeess (not verified)

Максимум внимания и разработанную индивидуальную программу для получения и последующего комфортного погашения кредита.
Решение о выдаче займа в день обращения

MichaelTit (not verified)

Вечные трастовые ссылки, размещение безанкорных ссылок, постинг.
Осуществляю работы по доработке сайта изнутри, Выявлю ошибки по сайту

За время продвижения увеличиваю конверсию сайта, нахожу первых клиентов; прорекламирую ваш сайт, интернет-магазин - делаю видимость вашего сайта за счёт обратных ссылок, которые увеличивают количество посетителей; размещаю информацию о вашем сайте соц.сетях, краудах (блогах, форумах, досках) продвижение - вы получаете первые результаты в течении месяца ( в кратчайшие сроки увеличивает посещаемость Вашего сайта в сотни раз, резко увеличивается не только прямой приток посетителей — кроме того, значительно повышаются позиции Вашего сайта в поисковых системах вплоть до лидирующих позиций.)
В продвижении сайта есть два пути развития. Первый — ждать, пока ваш проект самостоятельно поднимется по лестнице поисковой выдачи, что займет уйму времени и не принесет желаемого результата. Второй способ — потратить некоторые средства и значительно ускорить этот процесс.

MichaelTit (not verified)

Вечные трастовые ссылки, размещение безанкорных ссылок, постинг.
Осуществляю работы по доработке сайта изнутри, Выявлю ошибки по сайту

За время продвижения увеличиваю конверсию сайта, нахожу первых клиентов; прорекламирую ваш сайт, интернет-магазин - делаю видимость вашего сайта за счёт обратных ссылок, которые увеличивают количество посетителей; размещаю информацию о вашем сайте соц.сетях, краудах (блогах, форумах, досках) продвижение - вы получаете первые результаты в течении месяца ( в кратчайшие сроки увеличивает посещаемость Вашего сайта в сотни раз, резко увеличивается не только прямой приток посетителей — кроме того, значительно повышаются позиции Вашего сайта в поисковых системах вплоть до лидирующих позиций.)
В продвижении сайта есть два пути развития. Первый — ждать, пока ваш проект самостоятельно поднимется по лестнице поисковой выдачи, что займет уйму времени и не принесет желаемого результата. Второй способ — потратить некоторые средства и значительно ускорить этот процесс.

kamagra online ... (not verified)

Billig Cialis

JaimeTes (not verified)

Заказать бады для мужчин ради повышения либидо и потенции, китайская медицина.
Женские возбудители и афродизиаки. Однако дозволительно Обещать тогда :
fito-shop.in.ua

Spencer (not verified)

We are usually professional wholesale distributor of jerseys, focused in supplying Inexpensive Jerseys and personalized jerseys. Jerseys with 100% stitched traditional quality, all Amounts, Logos and Titles are sewn about and embroidered.
cheap jerseys online

MichaelTit (not verified)

Прорекламирую ваш сайт, интернет-магазин - делаю видимость вашего сайта из-за результата обратных ссылок, которые увеличивают количество посетителей; размещаю информацию о сайте соц.сетях, краудах (блогах, форумах, досках) продвижение - вы получаете первые результаты в течении месяца, источники ссылок - профиль, топики, комментарии.
Неизбежный рост Яндекс ИКС. Ускоренная индексация сайта поисковыми системами.
В 1-ый месяц увеличивается количество посетителей на ваш сайт, некоторые ключевые запросы попадут в ТОП.
SAYT-RF.RU — сильная команда профессионалов и дружная семья.
Мы ценим доброту и порядочность.

Доведем вас в Топ с точностью до миллиметра..

MichaelTit (not verified)

Прорекламирую ваш сайт, интернет-магазин - делаю видимость вашего сайта из-за результата обратных ссылок, которые увеличивают количество посетителей; размещаю информацию о сайте соц.сетях, краудах (блогах, форумах, досках) продвижение - вы получаете первые результаты в течении месяца, источники ссылок - профиль, топики, комментарии.
Неизбежный рост Яндекс ИКС. Ускоренная индексация сайта поисковыми системами.
В 1-ый месяц увеличивается количество посетителей на ваш сайт, некоторые ключевые запросы попадут в ТОП.
SAYT-RF.RU — сильная команда профессионалов и дружная семья.
Мы ценим доброту и порядочность.

Доведем вас в Топ с точностью до миллиметра..

www.hfaventolin.com (not verified)

It’s the best pharmacopoeia around. Each is pleasant. As strict as ever. Craig called my mom and helped her retain net on her meds. My tranquillity ran revealed of his meds and was cheerfully helped excuse after hours.
Cheers. Numerous content.

Antwan (not verified)

We are usually professional wholesale dealer of jerseys, customized in supplying Low cost Jerseys and customized jerseys. Jerseys using 100% stitched authentic quality, all Numbers, Logos and Titles are sewn in and embroidered.
online jerseys china

KevinFaply (not verified)

270906637

Quincy (not verified)

We are usually professional wholesale dealer of jerseys, focused in supplying Low cost Jerseys and custom-made jerseys. Jerseys with 100% stitched real quality, all Numbers, Logos and Labels are sewn in and embroidered.
wholesale nfl jerseys

Brycemab (not verified)

Amazing. You are making me very horny.
"Well I certainly can be";""
Sinn is so hott love her scenes
tennessee.pw/ass fingering/lesbian girl in thigh highs ass

Dude porn that went offline

EarleNeery (not verified)

Yes, we had a good time together yesterday. We haven’t seen each other very often lately, well, you yourself know why,” he answered, and taking her breasts in his palm, he felt her heart beating.
Katya whispered into Seryozha's ear and left the room picking up all the linen. When asked how everything went, Kate replied:
massageescort.biz/algeria/constantine
Then we went back to the lawn. Rita made me change my shorts for swim shorts. When they lay down, she suggested a game: they kiss me in turn with Inna, and whoever does better wins and makes a wish.
Very good. You clever. You are doing great. You are doing great. You suck very well. I repeated to her.
A minute later she returned, clutching a weightless fabric in her hand, and so that I could see how she was dressing, she stood in front of the window. She slowly pulled on her panties: strict, covering both the ass and the pubis, if not for one thing: they were transparent through and through, flesh-colored, therefore, they hid everything, but did not hide anything.
Vika was taller than everyone, but her chest was a maximum of one. They graduated together with Ira and were going to enter the same university. She looked like a model, and she moved like that, at least she tried, sometimes it caught the eye of others, or maybe she showed off so much in front of the guys, no one knew. A blonde with regular face shapes, in a black bathing suit, haunted guys like Slava and Ivan.

EarleNeery (not verified)

Poteklaaa ... joyfully he said aloud.
I wanted to pull away from my mother, but another blow to the back with a whip prevented me from doing so.
Thanks. You are incredibly beautiful too. I've probably said this before, but I'd say it a thousand more times.
"Bitch" I gently said, "But damn beautiful and very smart. That's why I love her!"
massageescort.biz/norway/sarpsborg
Vital, please pour some mineral water - exhaustingly - Vera asked in a trembling voice.
Girls, our Sanka pleased me so much today that I was in seventh heaven! And looking at me, happily added. Well done, I did not even think that you are so good at doing!
And then what the hell is a dream, judging by the twitching of my naked and not quite sober wife, he, of course, is happy to try to pester her, since we are under the same blanket, and no clothes and no barriers for pestering, all erogenous zones on the nipples, navel, Lyudmilka's pubis, completely open, not covered by anything for harassment.
Galina appeared on time. It was, indeed, a young woman, even a girl (looking up to 25 years old). She was a tall brunette, about the size of me (about 180 centimeters), a little too big, her chest was a little small, and her butt was a little big. Otherwise, she could be called beautiful. It clearly read Ukrainian roots. Beautiful large dark eyes looked directly, intelligently and seriously. She was wearing black leather pants and a black sweater.
“Well, of course,” he said, smiling slyly and holding it out to me.

KwoerjUrict (not verified)

96d70ce

@336i@

EarleNeery (not verified)

Verka jumped onto the bed again and pulled Sasha, who was closer, to her.
I can see how excited you are.
Suddenly, he abruptly grabbed the back of my head and drove the cock with all his might. My throat opened up and through the urge to vomit, I felt him fucking my throat. With each entry, I felt my throat widen and my neck muscles tense. Saliva and secretions oozed from the corners of her lips, tears gushed from her eyes.
massageescort.biz/oman/rustaq-1
Galya - Yes. And I. So I say, we are all girls here, we have been together for the fifth month. Let's stop being bullshit. And it is clear that we are all doing this. Why should we pretend that we don’t know the type, we don’t hear. There Svetka almost every night, and you Nastya, not very often.
Damn, I'm sorry, I can't control it.
Seryozha, help me get out of the car, and then I'll hobble myself somehow.
We lay for another twenty minutes and suddenly Anton, seeing that the member had taken on a combat state again, got up and turned my booty to the door of the compartment and lifted my ass. I lifted her by bending my legs and he ran his finger into the still leaking pussy. I was informed in anticipation of the entry of a member again into me and trying to go to a meeting for a deeper entry. When he turned me around and put me on my knees. He abruptly thrust it into my ass and I was on the verge of an orgasm from one first movement.

Argentumiq (not verified)

Wе tеnd tо think of greаt thinkеrs and innovаtоrs аs sоloists, but thе truth is thаt the greatest innovаtivе thinking dоеsn't оccur in а vаcuum. Innоvation rеsults frоm collaboration.

Argentumiq (not verified)

Wе tend tо think оf great thinkеrs аnd innоvators as soloists, but the truth is that the greatest innоvаtivе thinking dоesn't occur in a vacuum. Innovаtiоn results frоm cоllаbоration.

LucilleNog (not verified)

Walking away is not always easy, and it always hurt, especially from the person that you loved the most. Shaquita M (QuitaBelle) Howard, Touching the Broken:
Walk Away