Static Queries

The most common SELECT queries in Drupal are static queries using the query() method of the database connection object.
Static queries are passed to the database almost verbatim.

Example:

$database = \Drupal::database();
$query = $database->query("SELECT id, example FROM {mytable}");
$result = $query->fetchAll();

Only very simple SELECT queries should use the static query() method. You should use a dynamic query if you need more complex queries, dynamic query generation, or variability.

Do not use this function for simple INSERT, UPDATE, or DELETE queries. They should be handled through insert(), update(), and delete() respectively. For more complex DELETE queries involving multiple tables, see Complex DELETE queries.

Arguments

The query() method of the database connection object takes three arguments:

• $query: the query to run. Use placeholders if necessary and wrap all table names in curly braces.
• $args: an array of placeholder values to substitute in the query.
• $options: an array of options to control query behavior (optional).

Table name prefixing

In static queries, all table names must be enclosed in curly braces {...}.

Wrapping table names in curly braces marks them so the database system can prepend a prefix string if needed. Prefixing allows multiple sites to run from a single database or, in some cases, share selected tables across sites. It is also necessary to prevent data leakage from the host site into tests.

Placeholders

Placeholders indicate where a literal value will be inserted into the query for execution. By separating them from the query string itself, we allow the database server to distinguish SQL syntax from user-provided values, protecting against SQL injection attacks.

$query = $database->query("SELECT id, example FROM {mytable} WHERE created > :created", [
  ':created' => REQUEST_TIME - 3600,
]);

The code above selects all mytable ids and examples created in the last hour (3600 seconds). The placeholder :created is dynamically replaced with the value of REQUEST_TIME - 3600 at the time the query is run.

A query can have any number of placeholders, but they must all have unique names, even if they contain the same value. Depending on usage, the array of placeholders may be inline (as above) or built separately and passed in. The array order does not matter.

Placeholders beginning with "db_" are reserved for internal use and must never be defined manually.

Placeholders must not be escaped or quoted regardless of their type. Because they are passed to the database server separately, the server can properly distinguish the query string from the value.

// WRONG (quotes around the :type placeholder)
$result = $database->query("SELECT example FROM {mytable} WHERE type = ':type'", [
  ':type' => 'mytype',
]);

// CORRECT (no quotes around the :type placeholder)
$result = $database->query("SELECT example FROM {mytable} WHERE type = :type", [
  ':type' => 'mytype',
]);

Placeholders cannot be used for column and table names. If obtained from untrusted input, they should be passed through $database->escapeTable().

Array placeholders

Drupal’s database layer includes an additional placeholder feature. If the value passed to a placeholder is an array, it will automatically be expanded into a comma-separated list, as will the corresponding placeholder. This means developers don't have to count the number of placeholders needed.

Example:

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids[])", [':ids[]' => [13, 42, 144]]);

The following two statements are equivalent to the one above:

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (:ids_1, :ids_2, :ids_3)", [
  ':ids_1' => 13, 
  ':ids_2' => 42, 
  ':ids_3' => 144,
]);

$result = $database->query("SELECT * FROM {mytable} WHERE id IN (13, 42, 144)");

Query options

The third parameter of the query() method is an options array that defines query behavior. Typically, only two directives are used by most queries. Other values are primarily for internal use.

The "target" key indicates the connection target. If not provided, the default is "default". The only other valid value is "replica", to indicate the query should run on a replica server, if one exists.

The "fetch" key defines how the returned records should be retrieved. Valid values include: PDO::FETCH_OBJ, PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, or a string representing a class name. If a string is specified, each record will be fetched into a new instance of that class. The behavior of the other values is defined by PDO: stdClass object, associative array, numeric array, or both. See http://php.net/manual/en/pdostatement.fetch.php. The default is PDO::FETCH_OBJ, and should generally be used unless there is a specific reason not to.

Example of querying a replica server and fetching results as an associative array:

$result = $database->query("SELECT id, example FROM {mytable}", [], [
  'target' => 'replica',
  'fetch' => PDO::FETCH_ASSOC,
]);

The result object returned by query() can be used to fetch each returned row and column. Example:

$sql = "SELECT name, quantity FROM goods WHERE vid = :vid";
$result = $database->query($sql, [':vid' => $vid]);
if ($result) {
  while ($row = $result->fetchAssoc()) {
    // Do something with:
    // $row['name']
    // $row['quantity']
  }
}

Complex DELETE queries

Using a static query is a simple and compact way to express a DELETE query that involves removing from multiple tables in a single statement.

Example:

$database = \Drupal::database();
$database->query("DELETE {table1}, {table2} FROM {table1} INNER JOIN {table2} ON {table1}.id = {table2}.id WHERE {table1}.id=:recno", [":recno" => 2]);

(Deletes a row from both table1 and table2)