Web & Game Development Tutorials
Unity, Django, Drupal, C#, Python…
Samuli Natri 2012.09.25
Entrepreneur. Software developer since the 90's.
Attended Helsinki University Of Technology (Computer Science) and Helsinki University (Social Sciences).

Drupal - How To Do Database Queries

How to do Database Queries with db_query and db_select.


Create an article in node/add/article and create a template for it: node--[nid].tpl.php:

In this example I wrote all the code in the template. You shouldn't write this kind of logic in templates. Instead you should prepare the data elsewhere like in the template.php file or custom module.

Now we can test queries easily inside this template:

Create dummy content with Devel module (users and content).


Basic fetch

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

$sql = 'SELECT n.title FROM node n';

In Drupal you can do it with db_query():

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 except curly braces are used around the { table }

Limit results

You can limit results like this in sql:

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

In Drupal you could do this:

$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.


In sql you use variables like this:

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

In Drupal you use placeholders like this:

$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 an extra 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:

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

And iterate through that exactly the same way:

foreach($result as $item) {
  print $item->title;

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

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


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

$result = db_select('node','n')

Here you don't use curly braces around the table. It's handled for you.

Limit results

$result = db_select('node','n')


$result = db_select('node','n')