Drupal 7 - Database Query Examples

How to do Database Queries with db_query and db_select.

SUMMARY

1. Setup

2. db_query

3. db_select

 

1. Setup

- Create an article node/add/article
- Create a template for it (node--[nid].tpl.php)

Now we can test queries easily inside this template:

- Create dummy content with Devel (Generate users/generate content).

 

2. db_query

Basic fetch

To fetch all node titles you would normally do this kind of sql statement

<?php
$sql
= 'SELECT n.title FROM node n';

?>

In Drupal you do it like this with db_query() :

<?php
if (!$page) { print l($node->title, "node/2") ; }

$result = db_query('SELECT n.title FROM {node} n');

foreach(

$result as $item) {
  print
$item->title;
}
?>

So pretty much the same expect curly brackets are used around the table { table } .

These add table prefix to your tables so that you can share your database across multiple sites.
 

Limit results

You can limit results like this in sql :

<?php
$sql
= 'SELECT n.title FROM node n LIMIT 0,20';

?>

In Drupal you would do this:

<?php
$result
= db_query_range('SELECT n.title FROM {node} n',0,20);
?>

In Drupal LIMIT is abstracted in db_query_range() because of syntax differences between different databases (like in MSSQL you have to write TOP(2) to get top two rows but in MySQL you would use LIMIT 2)
 

Variables

In sql you use variables like this:
 

<?php
$sql
= 'SELECT n.title, n.uid FROM node n WHERE n.uid = $uid LIMIT 0,20';

?>

In Drupal you use placeholders like this:

<?php
$result
= db_query_range('SELECT n.title, n.uid FROM {node} n WHERE n.uid = :uid',0,20,
                          array(
':uid' => $uid));

?>

Placeholders are used to add a security layer that inserts variables in a secure manner so you don't have to escape or quote them before they are added into the query.

Printing the values

I used foreach to iterate through the result set.

You could also get an array with this:

<?php
$result
= db_query_range('SELECT n.title, n.uid FROM {node} n WHERE n.uid = :uid',0,20,
                          array(
':uid' => $uid))->fetchAll();
?>

You can iterate through that exactly the same way:

<?php
foreach($result as $item) {
  print
$item->title;
}
?>

If you need just one item, you can use fetchField();

<?php
$result
= db_query_range('SELECT n.title, n.uid FROM {node} n WHERE n.uid = :uid',0,20,
                          array(
':uid' => $uid))->fetchField();
print
$result;

?>

3. db_select

You can also use more flexible but slower db_select() to fetch items.

Here is how to do same thing with db_select:

 

Basic fetch

<?php
$result
= db_select('node','n')
          ->
fields('n',array('title'))
          ->
execute();?>

Here you don't use brackets around the table. it's handled for you.
 

Limit results

<?php
$result
= db_select('node','n')
          ->
fields('n',array('title'))
          ->
range(0,20)
          ->
execute();
?>

Variables

<?php
$result
= db_select('node','n')
          ->
fields('n',array('title','uid'))
          ->
range(0,20)
          ->
condition('n.uid',$uid,'=')
          ->
execute();
?>

This tutorial shows how to set up Installation Profiles.

This tutorial shows how to apply a patch with GnuWin32 Patch.

Add comment

Helpful information. Fortunate me I found your web site by chance, and I'm stunned why this coincidence didn't happened earlier! I bookmarked it.
kate spade handbags http://davidwrowland.info/wp-content/uploads/katespade/kate_spade_photo_...
kate spade purses http://cyberniticsinfotech.in/wp-content/uploads/katespade/kate_spade_ou...
kate spade jewelry http://www.casinospill-pa-nett.com/wp-content/uploads/katespade/store_ka...
kate spade ny http://bridge-guitar.com/wp-content/uploads/katespade/kate_spade_envelop...
kate spade phone cases http://iyifikiratolyesi.com/wp-content/uploads/katespade/kate_spade_2015...
kate spade 財布 http://zionrugs.com/wp-content/uploads/katespade/discount_kate_spade_353...
kate spade typewriter bag for sale http://www.lapinede.org/wp-content/uploads/katespade/kate_spade_hoop_ear...
kate spade australia http://sweethomekoblenz.com/wp-content/uploads/katespade/laptop_kate_spa...
kate spade sunglasses uk http://www.kuantumbiyoenerji.com/wp-content/uploads/katespade/kate_spade...
kate spade bag http://www.donnaanna.it/wp-content/uploads/katespade/kate_spade_things_w...
kate shoes http://pseltd.co.uk/wp-content/uploads/katespade/kate_spade_new_york_201...
kate spade reading glasses http://tsgs-net.de/wp-content/uploads/katespade/kate_spade_phone_purse_3...
surprise kate spade http://strippers.com.ua/wp-content/uploads/katespade/wedding_shoes_kate_...
kate spade this is the year to http://amanahsupplier.com/wp-content/uploads/katespade/kate_spade_factor...
kate spade sunglasses http://www.shelterscapes.com/wp-content/uploads/katespade/kate_spade_sat...
kate spade shoes clearance http://www.handbaglover.co.uk/wp-content/uploads/katespade2/kate_spade_g...
kate spade diaper bag http://www.filmdedief.nl/wp-content/uploads/katespade/kate_spade_bag_rep...
kate spade outlet brasil http://dichvusonhanoi.com/wp-content/uploads/katespade/kate_spade_ur_115...
green kate spade wallet http://www.umursiparis.com/wp-content/uploads/katespade/kate_spade_vase_...
kate spade bags http://www.knowhowsomeluz.com.br/wp-content/uploads/katespade/kate_spade...
popular kate spade bags http://sd-it-service.de/wp-content/uploads/katespade/kate_spade_new_york...
dress kate spade http://www.yogamar.eu/wp-content/uploads/katespade/kate_spade_ny_careers...
kate spade dresses http://www.door6.co.uk/wp-content/uploads/katespade/kate_spade_app_33355...
kate spade handbags http://abeyewear.com/wp-content/uploads/katespade/kate_spade_boston_8138...
kate spade canada store http://www.moebelhaus-schmidt.de/wp-content/uploads/katespade/kate_spade...
cherry lane lacey wallet kate spade http://www.missartevent.fr/wp-content/uploads/katespade/kate_spade_watch...
red kate spade dress http://www.indigitaleweb.com/wp-content/uploads/katespade/kate_spade_pan...
kate spade bangle http://www.eokab.se/wp-content/uploads/katespade/kate_spade_bridal_dress...
kate space http://www.is-handwerk.de/wp-content/uploads/katespade/kate_spade_jobs_3...
kate spade fragrance http://www.bernyhouse.it/wp-content/uploads/katespade/vintage_kate_spade...
kate spade バッグ http://www.mansourbakhtiar.com/wp-content/uploads/katespade/kate_spade_t...
kate spade card holder http://www.eliastzikas.com/wp-content/uploads/katespade/kate_spade_handb...
kate space http://naturbol.no/wp-content/uploads/katespade/used_kate_spade_handbags...
kate spade bridal shower gifts http://prettycute.com.au/wp-content/uploads/katespade/kate_spade_novel_c...
kate spade shoes http://www.aldentedeli.co.za/wp-content/uploads/katespade/kate_spade_bla...
kate spade backpack http://www.rigops.co.uk/wp-content/uploads/katespade/kate_spade_bridal_d...
kate spade mikas http://www.havsbaden.nu/wp-content/uploads/katespade/kate_spade_dress_co...
nylon kate spade http://www.diranmode.com/wp-content/uploads/katespade/kate_spade_gold_po...
kate spade louie wristlet http://sculpture-museum.org/wp-content/uploads/katespade/kate_spade_blac...
coupons for kate spade http://www.myrealwealth.com/wp-content/uploads/katespade/buy_kate_spade_...
kate spade sweater dress http://davidwrowland.info/wp-content/uploads/katespade/gold_kate_spade_4...
katespade http://cyberniticsinfotech.in/wp-content/uploads/katespade/special_kate_...
kate spade surprise sale http://cyberniticsinfotech.in/wp-content/uploads/katespade/kate_spade_ne...
kate spade purse http://najwaboutique.com/wp-content/uploads/katespade/kate_spade_toronto...
kate spade stevie diaper bag http://bridge-guitar.com/wp-content/uploads/katespade/how_to_clean_a_kat...
kate spade watch http://zionrugs.com/wp-content/uploads/katespade/black_and_white_kate_sp...
kate spade a http://www.lapinede.org/wp-content/uploads/katespade/kate_spade_saturday...
kate spade bags discount http://sweethomekoblenz.com/wp-content/uploads/katespade/kate_spade_ues_...
cherry lane kate spade wallet http://www.marjonkappers.nl/wp-content/uploads/katespade/kate_spade_spec...
kate spade on sale handbags http://pseltd.co.uk/wp-content/uploads/katespade/kate_spade_wedding_bang...

kate spade saturday http://prettycute.com.au/wp-content/uploads/katespade/kate_spade_outlet_...

Thanks for every other wonderful post. The place else may just anybody get that type of information in such a perfect way of writing? I have a presentation subsequent week, and I am on the look for such info.
Itˇs actually a great and useful piece of information. Iˇm happy that you shared this helpful info with us. Please stay us informed like this. Thanks for sharing.
fantastic publish, very informative. I wonder why the other specialists of this sector don't notice this. You should proceed your writing. I am confident, you've a great readers' base already!
parajumper vest
parajumpers gobi heren
parajumper
parajumpers
parajumpers sale
parajumpers gobi women
parajumpers kids
parajumpers parkas
para jumpers
parajumpers sale
parajumpers carrier
parajumpers outlet
parajumpers
parajumpers jacka
parajumper fake
parajumpers
parajumper montreal
parajumpers heli
black parajumper
parajumper
parajumpers doudoune
parajumpers damen
parajumpers canada sale
parajumpers jacken herren
para jumpers
parajumper jacket toronto
parajumpers montreal
parajumpers damen
parajumpers fur policy
para jumper
parajumper men
doudoune parajumpers
parajumpers jacken outlet
parajumpers kids
parajumpers vinter 2013
parajumpers
parajumpers sale
parajumpers damen
parajumpers deutschland
parajumpers norge
para jumpers
parajumpers xxxl
parajumpers outlet
parajumpers
parajumpers big bend
parajumpers jacka
parajumpers jackets sale
parajumpers jacka
parajumper gobi review
parajumpers xxxl

parajumper desert http://www.onlinepd4educators.org/css/parajumper/fake_parajumpers_jacket...

Someone necessarily help to make critically posts I would state. That is the very first time I frequented your web page and to this point? I surprised with the analysis you made to make this actual submit incredible. Wonderful activity!
asics cumulus http://www.redhawkstv.com/scripts/asics/cheap_asics_29715.html
asics gel kayano 19 http://www.harvestis.com/styles/asics/asics_nimbus_25747.html
asics sneakers http://www.chaoshauntedhouse.net/scripts/asics/asics_onitsuka_tiger_2829...
asics kayano 18 http://www.strongmancorporation.com/scripts/asics/asics_gel_nimbus_14_19...
asics gel nimbus 14 womens http://www.linligroup.com/scripts/asics/asics_nimbus_1454.html
asics gt 2090 http://www.falconrattlermedia.com/scripts/asics/asics_nimbus_14_17389.html
womens asics gel kayano 19 review http://www.proactivecollections.com/scripts/asics/asics_nimbus_4042.html
asics gel saga http://www.healeylake.org/images/asics/asics_gel_blur_33_25032.html
asics gel kinsei 4 http://www.iasianlanguage.org/images/asics/cheap_asics_2513.html
asics gt 2000 http://www.ghq.org/scripts/asics/asics_kayano_5181.html
asics nimbus 13 http://www.jjlacupuncture.com/scripts/asics/asics_gel_kayano_18_7528.html
asics gel nimbus 12 http://www.mouwie.com/images/asics/asics_nimbus_27449.html
mens asics on sale http://www.viwebworks.net/images/asics/asics_kayano_19965.html
asics tennis shoes http://www.remitsearch.com/images/asics/asics_onitsuka_tiger_30753.html
asics gel nimbus 14 http://www.isolab.org/scripts/asics/asics_onitsuka_tiger_9765.html
asics womens shoes http://www.eltawheed.org/styles/asics/asics_gel_kayano_18_8659.html
asics enduro 6 http://www.appliedsymbolics.com/asics/cheap_asics_708.html
asics 2150 womens http://www.stbasilsboston.org/scripts/asics/asics_onitsuka_tiger_19747.html
cheap asics http://www.ewan-group.com/styles/asics/asics_kayano_30670.html
asics gel 170 tr http://www.dallasmartllc.com/styles/asics/asics_nimbus_12653.html
asics running shoes http://www.easecommercialservices.com/images/asics/asics_gel_nimbus_14_1...
asics shoe review http://www.northcoasthin.org/scripts/asics/asics_nimbus_14_28447.html
asics mens running shoes http://www.alexander-fink.com/scripts/asics/cheap_asics_1027.html
asics gel http://www.hairknowlogy.com/images/asics/cheap_asics_37366.html
asics onitsuka http://www.financialservices-online.com/images/asics/asics_gel_kayano_98...
asics dan gable ultimate http://www.starfroggames.com/scripts/asics/asics_gel_kayano_18_34971.html
bitcoin asic http://www.trillennium.com/scripts/asics/asics_nimbus_21924.html
asics gel platinum 4 http://www.everyday-manager.com/scripts/asics/asics_gel_blur_33_16691.html
asics gel nimbus 13 http://www.triciatoole.com/images/asics/asics_onitsuka_tiger_605.html
womens asics sneakers http://www.redhawkstv.com/scripts/asics/cheap_asics_25975.html
asics tiger http://www.harvestis.com/styles/asics/asics_gel_nimbus_14_21190.html
asics running shoes women http://www.chaoshauntedhouse.net/scripts/asics/asics_nimbus_14_23082.html
asics gt 2000 http://www.strongmancorporation.com/scripts/asics/asics_gel_nimbus_14_12...
asics nimbus 13 http://www.cuamps.com/images/asics/asics_nimbus_29379.html
asics youth http://www.financialservices-online.com/images/asics/asics_onitsuka_tige...
asics kayano http://www.adminmachine.com/scripts/asics/asics_gel_blur_33_28793.html
asic shoes http://www.trillennium.com/scripts/asics/cheap_asics_31351.html
asic verification jobs http://www.rayharwood.com/images/asics/asics_gel_kayano_18_5617.html
asics gel kayano http://www.triciatoole.com/images/asics/asics_gel_saga_8901.html
asics gel lyte 33 reviews http://www.stephenecovertcpa.org/styles/asics/asics_gel_blur_33_10571.html
asics 15 nimbus http://www.shelbyrobertson.com/scripts/asics/asics_kayano_12466.html
asics women running shoes sale http://www.chaoshauntedhouse.net/scripts/asics/cheap_asics_27108.html
asics nimbus 14 http://www.strongmancorporation.com/scripts/asics/asics_gel_kayano_14976...
asics gel shoes http://www.linligroup.com/scripts/asics/asics_gel_saga_34610.html
asics gel saga http://www.stonetowerminiatures.com/images/asics/asics_gel_nimbus_14_285...
asics kayano 19 http://www.aiondiscount.com/images/asics/asics_kayano_19189.html
asics 2170 http://www.healeylake.org/images/asics/cheap_asics_22190.html
asics gt 2000 http://www.iasianlanguage.org/images/asics/cheap_asics_33738.html
asics america http://www.ghq.org/scripts/asics/asics_gel_nimbus_14_34353.html
asics gel kayano 17 http://www.jjlacupuncture.com/scripts/asics/asics_onitsuka_tiger_330.html

asics kinsei 4 http://www.ewan-group.com/styles/asics/cheap_asics_16958.html

thanks!!

I found very difficult to learn drupal and drupla not providing proper documentation and your site help me a lot thanks