Working with a Database in Drupal 7 - Lesson 3 - Static Queries (SELECT)

The most common form of a query in Drupal is a static query. A static query is passed directly to the database as-is. Only SELECT queries can be static.

Use static queries only for very simple operations. For more complex, dynamically constructed, or modifiable queries, you should use dynamic queries.

The simplest way to execute a static query is via the query method:

query("SELECT nid, title FROM {node}");
?>

The preferred approach is to use the procedural wrapper:

This call to db_query() is equivalent to:

query("SELECT nid, title FROM {node}");
?>

Using db_query() is better than calling Database::getConnection() directly.

db_query() accepts three arguments. The first is the SQL query string, with placeholders and table names wrapped in curly braces. The second is the array of placeholders. The third, optional, is an array of options for query execution.

Prefixing

In static queries, all table names should be wrapped in curly braces {}. This allows Drupal to apply table prefixes, which support multisite installations sharing a database with table name prefixes like site1_ or site2_.

Placeholders

Placeholders are marked by colons (e.g., :created) and help separate user input from SQL syntax, preventing SQL injection.

 :created", array(
  ':created' => REQUEST_TIME - 3600,
));
?>

In this example, the query fetches all nodes created in the past hour. The :created placeholder is replaced with the computed time.

Placeholder values can be provided inline or via a predefined array. They must all have unique names, even if values repeat. Reserved names starting with db_ should not be used.

Note: Do not wrap placeholders in quotes unless you're dealing with string literals. Example:

 'page',
));

// CORRECT:
$result = db_query("SELECT nid, title FROM {node} WHERE type = :type", array(
  ':type' => 'page',
));
?>

Placeholders cannot be used for table or column names – only for values.

Arrays as Placeholders

Drupal’s database layer supports array placeholders. When passed an array, placeholders are expanded automatically into comma-separated lists. This eliminates the need to count and name each placeholder manually.

 array(13, 42, 144)));

// Which becomes:
db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
  ':nids_1' => 13,
  ':nids_2' => 42,
  ':nids_3' => 144,
));

// Equivalent to:
db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)");
?>

Query Options

The third argument to db_query() is an array of query options that control how the query is executed. The most common options are:

• 'target' — Defines the database target, such as 'slave' for read replicas (default is 'default').
• 'fetch' — Defines how results are fetched. Supported values include:
  • PDO::FETCH_OBJ — as object (default)
  • PDO::FETCH_ASSOC — as associative array
  • PDO::FETCH_NUM — as numeric array
  • PDO::FETCH_BOTH — as both associative and numeric array
  • Class name string — as an object of that class

More info: PDO::fetch documentation.

Example using a read replica and fetching results as an associative array:

 'slave',
  'fetch' => PDO::FETCH_ASSOC,
));
?>