1. Blogs
  2. tomo's blog
  3. Querying the database in Drupal 7 using db_select

Querying the database in Drupal 7 using db_select

sql mysql php database
By tomo on March 4, 2016 - 11:11am

Drupal, unlike plain old blog software, gives you the ability to create custom content object types with backing storage in the database as custom tables, doing all this without requiring any programming knowledge. When you create a new custom Content Type and add fields to it, in effect, you are creating a new database table for those fields.

Drupal also gives you ways to access and query the data without writing code. The most powerful form of this is Views, which every Drupal site should install as soon as starting. In the Views UI, you can select which fields (database columns) you want to retrieve and then create your filter (WHERE conditions), and even join other tables via Views's relationships. MySQL "ORDER BY" is accomplished via Views's sorting. And Views will handle counting, limiting, and paging for you. Of course, there are limits to what can be done through this general UI, such as complex logic with ORs, or any custom clauses.

<?php
    $result = db_select('node', 'n')
    ->fields('n')
    ->condition('nid', $node->nid,'=')
    ->condition('status', 0,'>')
    ->condition('uid', array(1,5,7),'IN')
    ->execute()
    ->fetchAssoc();
?>

Drupal has always had a database querying API. This API has been updated for Drupal 7, although the function names are the same or similar. What's new in Drupal 7 is that querying is done using objects, so creating a SELECT statement is done by getting a SelectQuery object and then adding clauses to it via the object's methods. You call db_select($tablename) to create the SelectQuery object and you can immediately alter the object to build up your query with methods fields, condition, join, etc. and then finally execute followed by fetchAssoc (to run the query and, obviously desired for any select query, get the returned rows in a PHP array).

You can chain many of these methods into one PHP statement, e.g.

<?php
$result = db_select('mytable')
  ->fields('mytable')
  ->condition('myfield', 'myvalue')
  ->execute();
?>

You can't chain a method which doesn't return the SelectQuery object again and there's a list of those functions (like addField and leftjoin) here

Here's your starting point to read further about db_select

For other SQL statement types like UPDATE or INSERT, there's the equivalent db_update and db_insert. This is more explicit than db_query.

So in Drupal 7, when you write queries in code you are creating them without explicitly writing SQL. You will still need to already know the equivalent concepts for the methods being used (fields/columns, joins, types of conditions, types of sorting). But it will be harder for you to write a bad query and querying via this API will help you make a valid query. This concept is similar to Views, but with much more flexibility. This should also help protect against SQL injections.

More articles