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();
Comments
It works, yeah?
Be sure it is ;)
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Wow nice and excellent article... all information explanation and program examples are very clear so easy to understand the concepts
Nice and to the point article.. great....
It's very very nice and useful. Easy to understand
Hi there! cialis vs viagra good website.
free sex cam sites adultfreewebcamsites.com top cam sites.
live cam girls camgirls1.com korean cam girls.
webcam sex show hot sexy cam free couple live sex cam.
cheap cam sites porn cam sites popular webcam sites.
Hello! erectile dysfunction great internet site.
Hello! erectile dysfunction great internet site.
Hello! erectile dysfunction great internet site.
Hello! erectile dysfunction great internet site.
Howdy! buy clomid pct uk very good web site.
Howdy! buy clomid pct uk very good web site.
Howdy! buy clomid pct uk very good web site.
Hi! dapoxetine very good website.
Hello! how does cialis work great web page.
Hello! how does cialis work great web page.
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
дом 2 ютуб
сайт дома два
девушки дом 2
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Смотреть фильм онлайн Последний бросок онлайн посмотреть в хорошем качестве предлагаем
смотреть фильмы и сериалы в хорошем качестве
Howdy! buy clomid fertility drug excellent site.
Howdy! buy clomid fertility drug excellent site.
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Психолог онлайн
https://www.instagram.com/batmanapollo/
https://www.instagram.com/batmanapollo/ - Психолог онлайн
Howdy! where buy priligy beneficial web page.
лайф дом 2
дом 2 сайт
дом 2 серии онлайн
Howdy! where buy priligy beneficial web page.
Howdy! where buy priligy beneficial web page.
дом 2 смотреть
дома 2 онлайн
смотреть эфиры дом 2
Hello there! ed medications excellent site.
Hello there! ed medications excellent site.
Hello there! buy clomid bodybuilding great internet site.
cam 2 cam sex free webcam sex love sex cam.
free sex cam sites adultfreewebcamsites naked webcam sites.
Howdy! where can i buy colchicine in the uk excellent site.
Howdy! where can i buy colchicine in the uk excellent site.