Drupal 7 basic db_querys

 

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() :

<?phpif (!$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:

<?phpforeach($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();?>