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.

Back to top

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.

Back to top

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
}
Back to top

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.

Back to top

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();
Back to top

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();
Back to top

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.

Back to top

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.