Defining Database Parameters

$db_url = 'mysql://username:password@localhost/databasename';

■NoteIf you are in a situation where you are writing a stand-alone PHP script or you have existing PHP code outside of Drupal that needs access to Drupal’s database, you will want to want to call include_once ('includes/bootstrap.inc') and then call drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE) to generate an active connection. At that point, you can use db_query(), as explained in the next section.

Performing Simple Queries

db_query('SELECT * FROM {joke} WHERE vid = %d', $node->vid);

db_query("INSERT INTO {joke} (nid, vid, punchline) VALUES (%d, %d, '%s')",

$node->nid, $node->vid, $node->punchline);

db_query("UPDATE {joke} SET punchline = '%s' WHERE vid = %d", $node->punchline,

$node->vid);

db_query('DELETE FROM {joke} WHERE nid = %d', $node->nid);

Retrieving Query Results

There are various ways to retrieve query results depending on whether you need a single row or the whole result set, or you are planning to get a range of results for internal use or for display as a paged result set.

1．Getting a Single Value

$sql = "SELECT COUNT(*) FROM {node} WHERE type = 'blog' AND status = 1";

$total = db_result(db_query($sql));

2．Getting Multiple Rows

$sql = "SELECT * FROM {node} WHERE type = 'blog' AND status = 1";

$result = db_query(db_rewrite_sql($sql));

while ($data = db_fetch_object($result)) {

       $node = node_load($data->nid);

       print node_view($node, TRUE);

}

The preceding code snippet will print out all published nodes that are of type blog. (The status field in the node table is 0 for unpublished nodes and 1 for published nodes.) We will cover db_rewrite_sql()shortly. The db_fetch_object()function grabs a row from the result set as an object. To retrieve the result as an array, use db_fetch_array(). The practice of retrieving rows as objects is common since most developers prefer its less verbose syntax.

3．Getting a Limited Range of Results

$type = 'blog';

$status = 1;

$sql = "SELECT * FROM {node} n WHERE type = '%s' AND status = %d ORDER BY

n.created DESC";

$result = db_query_range(db_rewrite_sql($sql), $type, $status, 0, 10);

4．Getting Results for Paged Display

$sql = "SELECT * FROM {node} n WHERE type = 'blog' AND status = 1 ORDER BY

n.created DESC"

$result = pager_query(db_rewrite_sql($sql), 0, 10);

while ($data = db_fetch_object($result)) {

$node = node_load($data->nid);

print node_view($node, TRUE);

}

// Add links to remaining pages of results.

print theme('pager', NULL, 10);

Although pager_query() is not really part of the database abstraction layer, it is good to know when you need to create a paged result set with navigation. A call to theme('pager') at the end will display the navigation links to the other pages. You don’t need to pass the total number of results to theme('pager') because the number of results is remembered internally from the pager_query() call.

5．Deleting Tables on Uninstall

The Administer ➤ Modules page has an Uninstall tab that not only allows modules to be disabled,but also removes their data from the database. If you want to enable the deletion of your module’s tables on this page, implement the uninstall hook in your module’s .install file. You might want to delete any variables you’ve defined at the same time.

function annotate_uninstall() {

db_query("DROP TABLE {annotations}");

variable_del('annotate_nodetypes');

}

6．Writing Your Own Database Abstraction Layer

First, we make a copy of includes/database.mysql.inc and rename it as

includes/database.dnabase.inc. Then we change the logic inside each wrapper function to map to DNAbase’s functionality instead of MySQL’s functionality. When all is said and done, we have the following functions declared in our file:

_db_query($query, $debug = 0)

db_affected_rows()

db_connect($url)

db_decode_blob($data)

db_distinct_field($table, $field, $query)

db_encode_blob($data)

db_error()

db_escape_string($text)

db_fetch_array($result)

db_fetch_object($result)

db_lock_table($table)

db_next_id($name)

db_num_rows($result)

db_query_range($query)

db_query_temporary($query)

db_result($result, $row = 0)

db_status_report($phase)

db_table_exists($table)

db_unlock_tables()

db_version()