Querying the database in Drupal 7 requires using the database.inc API.  This is to provide a modular interface to get information from the database without depending on the type of database that serves as the backend for the drupal installation.  In other words, it does not matter if the database is either MySQL or PostgreSQL, the database API provides the same functions and objects to query information from it.  Essentially, there are three functions to query information from the database:

  1. db_query
  2. db_query_range
  3. db_select

This article will provides an example on how to use db_select.  This function gets information from a specific table in the database and returns a new SelectQuery object, which provides several tools to define the query, e.g. condition, where, distinct, countQuery.  You will perform the query using the execute function.  Then, you typically will iterate through the result using the fetch functions.

Let's first build a query that gets information from the node table, which has these columns:

mysql> describe node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid       | int(10) unsigned | YES  | UNI | NULL    |                |
| type      | varchar(32)      | NO   | MUL |         |                |
| language  | varchar(12)      | NO   | MUL |         |                |
| title     | varchar(255)     | NO   | MUL |         |                |
| uid       | int(11)          | NO   | MUL | 0       |                |
| status    | int(11)          | NO   | MUL | 1       |                |
| created   | int(11)          | NO   | MUL | 0       |                |
| changed   | int(11)          | NO   | MUL | 0       |                |
| comment   | int(11)          | NO   |     | 0       |                |
| promote   | int(11)          | NO   | MUL | 0       |                |
| sticky    | int(11)          | NO   |     | 0       |                |
| tnid      | int(10) unsigned | NO   | MUL | 0       |                |
| translate | int(11)          | NO   | MUL | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

(When working with the database in Drupal, it will be very useful to view the database structure from either the command line or PHPMyAdmin. This will help you in determining which table to query and its structure, especially in designing the conditions and fields to fetch.)

$result = db_select('node', 'n')
                  . . .

The first parameter selects the table to query and the second parameter sets an alias for this table, which is used in the conditions and fields functions of the Select Query object.

db_select($table, $alias = NULL, array $options = array())


Now, add conditions to the query, which only fetch published articles:

$result = db_select('node', 'n')
                  ->condition('type', 'article')
                  ->condition('status', '1')
                  . . .


Add some fields in which to return:

$result = db_select('node', 'n')
                  ->condition('type', 'article')
                  ->condition('status', '1')
                  ->fields('n',array('title','nid'))
                  . . .


In this example, let's limit the nodes fetched to the first five:

$result = db_select('node', 'n')
                  ->condition('type', 'article')
                  ->condition('status', '1')
                  ->fields('n',array('title','nid'))
                  ->range(0,5)
                  . . .


Finally, execute the query:

$result = db_select('node', 'n')
                  ->condition('type', 'article')
                  ->condition('status', '1')
                  ->fields('n',array('title','nid'))
                  ->range(0,5)
                  ->execute();


Ok, so you have the results, but how do you see them? You must iterate through them by fetching each one:

$result = db_select('node', 'n')
                  ->condition('type', 'article')
                  ->condition('status', '1')
                  ->fields('n',array('title','nid'))
                  ->range(0,5)
                  ->execute();
 
while($row=$result->fetchAssoc()) {
        dpr($row);
}

(If you don't have dpr, you will need to install the devel module. Otherwise, you can use print_r or var_export.)

The output for this example is this: