hpw to operate DB using drupal

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 whether you are planning to get a range of results for internal use or
for display as a paged result set.
Getting a Single Value
If all you need from the database is a single value, you can use db_result() to retrieve that
value. Here is an example of retrieving the total number of users who have not been blocked
by the administrator (excluding the anonymous user):
$count = db_result(db_query('SELECT COUNT(uid) FROM {users} WHERE status = 1
AND uid != 0'));
Getting Multiple Rows
In most cases, you will want to return more than a single field from the database. Here is a
typical iteration pattern for stepping through the result set:
$type = 'blog';
$status = 1; // In the node table, a status of 1 means published.
$sql = "SELECT * FROM {node} WHERE type = '%s' AND status = %d";
$result = db_query(db_rewrite_sql($sql), $type, $status);
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, as opposed to arrays, is common since most developers prefer its
less verbose syntax.
Getting a Limited Range of Results
As you might guess, running the preceding query on a site with, say, 10,000 blog entries is a
dangerous idea. We’ll limit the result of this query to only the ten newest blog entries:
$type = 'blog';
$status = 1; // In the node table, a status of 1 means published.
$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);
while ($data = db_fetch_object($result)) {
$node = node_load($data->nid);
print node_view($node, TRUE);
}
94 CHAPTER 5 n WORKING WITH DATABASES
Instead of passing the query to db_query() and using the LIMIT clause, we instead use
db_query_range(). Why? Because not all databases agree on the format of the LIMIT syntax, so
we need to use db_query_range() as a wrapper function.
Note that you pass the variables that will fill placeholders before the range (so the type
and status are passed before 0 and 10 in the example just shown).
Getting Results for Paged Display
We can present these blog entries in a better way: as a page of formatted results with links to
more results. We can do that using Drupal’s pager (see Figure 5-2). Let’s grab all of the blog
entries again, only this time we’ll display them as a paged result, with links to additional pages
of results and “first” and “last” links at the bottom of the page.
$type = 'blog';
$status = 1;
$sql = "SELECT * FROM {node} n WHERE type = '%s' AND status = %d ORDER BY
n.created DESC";
$pager_num = 0; // This is the first pager on this page. We number it 0.
$result = pager_query(db_rewrite_sql($sql), 10, $pager_num, NULL, $type,
$status);
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, $pager_num);
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.