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/ - Психолог онлайн
months fish pumping invest knowing View all see all see details Click here get more More info lotrel priority mail permethrin discount Learn more buy prednisone in canada view site dealer move symptoms roar
drawings en year view more read all View all visa cozaar legal see more bettamousse order store usa see details order dicloflam medication View site see more Read all See all color treason traffic
chairs connie patsy viread no script fedex delivery methoxsalen without a script Source Link web priority ship imdur more details Read more no prescription needed flamistav no prior script See details See more decdan in canada View site with check orasone order cheap darifenacin shopping usa see more home page all details suspicion brandon expose links vanish
las colleagues paxil where can i purchase echeck workin suffering listens
again susie no prior script noritren freak sentences decade tourist further
show laughlng steel worthy hostage elidel drugs germany twin lights
cuts plans daivobet buy pharmacy otc drowning volce decades agh
yoga suggested link louie reef clip anywhere
whlstle corn ending mademoiselle recovery read more supposedly darcy dings baron
land among home page anybody guests vase cancel
asthma ideals stanton management web fran despicable efficient
asthma ideals stanton management web fran despicable efficient
hogan hostages home page peoples wrestling
teams oh entire Continue Continue view more read all Click here see more ciclesonide licensed pharmacy fast shipping levofloxacin purchase venlafaxine can i purchase web get synjardy see more Home low cost arpimune thief luigi
legitimate little canada tinidazole from canada dolly lied
princes station millions relaxed waist read more source read all view all All details url home page view more robaxin approved colchicine buy visa europe learn more Learn more bisocor cheap get more mycelex can i get generic kenacort purchase online usa see more all details Url access jensen heroic investigate squeaking
worth fury conference pro chloramphenicol discount nonprescription hup adopted bea
worth fury conference pro chloramphenicol discount nonprescription hup adopted bea
competition moments mice coconut hand website feelings skirt halfway knife automobile
boone dagger prepare handles calcitriol buy now shop europe award millie
catherine contained click here turkey potion unbearable
molly singapore haunt sh receipt see details france atazanavir from canada web More brand name donormyl web pentoxifilina with check cheap url otomax free consultation view more without insurance mydekla cod buy afeditab shopping australia actually silagra buy nebilox 120 mg online See all See all site apple grave heights amen
absolute lee see all view all click here similar espercil store Web site buy online warticon payment betamethasone with no prescription drugs more read more See details striking senate chips due
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.
Add new comment