Database Changes in Drupal 7 from Drupal 6

Drupal 7 introduces a completely new database API, utilizing a number of dynamic query builders and formal prepared statements. The following Drupal 6 functions and hooks were removed: db_affected_rows(), db_distinct_field(), db_error(), db_last_insert_id(), db_placeholders(), db_lock_table(), db_prefix_tables(), db_result(), db_fetch_*(), db_version(), db_rewrite_sql(), hook_db_rewrite_sql(), pager_query(), tablesort_sql(), and others.

For full information, read the Database API guide. A few common examples of Drupal 6 to 7 conversion are also covered below.

Normal SELECT queries:

<?php
 // Drupal 6 
$result = db_query("SELECT nid, title FROM {node} WHERE uid = %d AND type IN (" . db_placeholders(array('page', 'story'), 'varchar') . ")", 5, 'page', 'story');

// Drupal 7 
$result = db_query("SELECT nid, title FROM {node} WHERE uid = :uid AND type IN (:type)", array(
  ':uid' => 5,
  ':type' => array('page', 'story'),
));
?>

Iterating a result set from db_query():

<?php 
// Drupal 6
 while ($record = db_fetch_object($result)) {
  // Do stuff with $record, which is an object}

// Drupal 7
 foreach ($result as $record) {
  // Do stuff with $record, which is an object}
 ?>

Insert statements:

<?php 
// Drupal 6 
db_query("INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (%d, '%s', %f)", 5, 'hello world', 3.14);$id = db_last_insert_id();

// Drupal 7 
$id = db_insert('mytable')
  ->fields(array(
    'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14,
  ))
  ->execute(); 
?>

Update statements:

<?php 
// Drupal 6 
db_query("UPDATE {node} SET title='%s', status=%d WHERE uid=%d", 'hello world', 1, 5);

// Drupal 7 
db_update('node')
  ->fields(array('title' => 'hello world', 'status' => 1))
  ->condition('uid', 5)
  ->execute();
 ?>

Delete statements:

<?php 
// Drupal 6 
db_query("DELETE FROM {node} WHERE uid=%d AND created < %d", 5, REQUEST_TIME - 3600);

// Drupal 7 
db_delete('node')
  ->condition('uid', 5)
  ->condition('created', REQUEST_TIME - 3600, '<')
  ->execute(); 
?>

Also note that dynamic queries that have tags may be altered by any module that implements hook_query_alter() or hook_query_TAG_alter(). This takes the place of hook_db_rewrite_sql() in Drupal 6.

file_validate_extensions() enforces check for uid=1

(issue) In 6.x the function for validating file extensions would bypass this check for the uid=1 user. This has been removed in 7.x. If your module depended on this behavior you need to check the user's uid and conditionally specify the file_validate_extensions() validator.