Drupal 7 Cheat Sheet: The Database

Photo of Greg Harvey
Tue, 2010-03-30 10:29By greg

This is a quick post while it's fresh in my head, regarding porting your contributed modules to the Drupal 7 API. This post specifically focusses on database manipulation and provides the like-for-like changes. If anything here is wrong, or could be done better, please let me know!

First up, individual results:

/*
* Remember this?
*/
$value = db_result(db_query("SELECT some_field FROM {some_table}"));

/*
* Forget it! It now looks like this:
*/
$value = db_query("SELECT some_field FROM {some_table}")->fetchField();
?>

Selects still look pretty much the same, at first glance, but wait:

/*
* Let's fetch a database object in Drupal 6:
*/
$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = %d", $some_var);
while ($row = db_fetch_object($result)) {
// Loop through your query results and do some stuff.
}

/*
* In Drupal 7 there is no db_fetch_object() or db_fetch_array():
*/
$result = db_query("SELECT some_field FROM {some_table} WHERE some_other_field = :some_other_field", array(':some_other_field' => $some_var));
foreach ($result as $row) {
// Loop through your query results and do some stuff.
}
?>

Deletes and updates now look quite different. You don't use db_query() at all any more for those types of SQL query. Each one has its own query building function:

/*
* A delete query in Drupal 7:
*/
db_delete('some_table')
->condition('some_field', $some_var)
->execute();

/*
* An update query in Drupal 7:
*/
db_update('some_table')
->fields(
array(
'some_field' => $some_var,
'some_other_field' => $some_other_var,
)
)
->condition('some_identifying_field', $some_id_var)
->execute();
?>

You should already be using drupal_write_record() for inserts anyway, and it has not changed, as far as I can tell.

Good news is hook_schema() and the related functions don't seem to have changed at all, so many of you won't need to touch installation scripts, unless you need to manipulate data as part of the update process, of course.

And that's that for now. Hope it's useful.