A Drupal 7 SQL cheat sheet

I'm currently developing a new Drupal 7 application (technically built as a new Drupal module), and as I'm building the application, I've made notes on database queries I've made.

The approach I'm taking is to use the following Drupal SQL query functions. As you can see from the function names, there are now different Drupal functions for each query type:

• SELECT - use db_query
• INSERT - use db_insert
• UPDATE - use db_update
• DELETE - use db_delete

Note that you can also execute some of these queries with the drupal_write_record function, but I've been having problems getting that to work, so I'm using these functions instead.

Given that background, here is my Drupal 7 SQL cheat sheet, with SQL SELECT, INSERT, UPDATE, and DELETE examples.

Drupal 7 SQL SELECT example - one row returned

Here's one way to execute a Drupal 7 SQL SELECT statement with the db_query function:

$q = "SELECT * FROM {projects} WHERE id = :id AND user_id = :uid";
$project = db_query($q, array(':id' => $id, ':uid' => $uid))
           ->fetchObject();

# returns an object with fields corresponding to the names
# of your database table, which you access like
# $project->name, $project->description, and so on.

Because I'm limiting this query by the 'id' field, I know I'm only going to get one record back. (In fact it may fail to find any records, but I'm trying to keep this simple.)

As a better example of how db_query() returns an object, here's another example:

# make sure the user owns the project id
$q = "SELECT user_id FROM {projects} WHERE id = :project_id";
$result = db_query($q, array(':project_id' => $project_id))->fetchObject();
echo 'user_id is ' . $result->user_id;

As you can see, I treat $result as an object after the query, knowing that it will have a field named user_id, because that's the name of the database table field I'm asking for in my query.

Drupal 7 SQL SELECT example - multiple rows returned

Here's how you issue a Drupal 7 SQL SELECT query with db_query and handle the results when you are expecting many rows to be returned:

$q = "SELECT * FROM {projects} WHERE uid = :uid";
$result = db_query($q, array(':uid' => $uid));
foreach ($result as $row) {
  // loop through your result set, working with $row here
}

Drupal 7 SQL INSERT with db_insert

How to execute a SQL INSERT with db_insert:

$id = db_insert('projects')
    ->fields(array(
        'user_id' => $uid,
        'project_type' => $project_type,
        'name' => $name,
        'description' => $description,
        'last_updated' => $timestamp,
        'date_created' => $timestamp
    ))
    ->execute();

With a SQL INSERT like this, the db_insert function returns the value of the SERIAL (auto_increment) field, which is very nice.

Drupal 7 SQL UPDATE with db_update

How to execute a Drupal 7 SQL UPDATE with db_update:

db_update('projects')
	->fields(array(
	        'project_count_type' => $project_count_type,
	        'name' => $name,
	        'description' => $description,
	        'last_updated' => $timestamp,
	        )
	)
	->condition('id', $id)
	->execute();

Drupal 7 SQL DELETE with db_delete

How to execute a Drupal 7 SQL DELETE with db_delete:

db_delete('projects')
  ->condition('id', $id)
  ->execute();

Drupal 7 SQL cheat sheet - Summary

As you have seen, you use these Drupal 7 functions for the corresponding query types:

• SELECT - use db_query
• INSERT - use db_insert
• UPDATE - use db_update
• DELETE - use db_delete

I hope these Drupal 7 SQL query examples have been helpful. I'll try to keep updating this cheat sheet as I learn more.