Static queries
The most common form of query in Drupal is a static query. A static query is one that will be passed to the database nearly verbatim. Only Select queries may be static.
The internal way to issue a static query is with the query method:
<?php
$result = $conn->query("SELECT nid, title FROM {node}");
?>The procedural wrapper is generally preferred:
<?php
$result = db_query("SELECT nid, title FROM {node}");
?>Calling db_query() as above is equivalent to the following:
<?php
$result = Database::getConnection()->query("SELECT nid, title FROM {node}");
?>(See why the procedural version is preferred?)
db_query() takes three arguments. The first is the query string, using placeholders where appropriate and denoting all table names with curly braces. The second is an array of placeholder values. The third is, optionally, an array of configuration directives to dictate how the query will be run.
Prefixing
In static queries, all table names must be wrapped in {}. That flags them so that the database system can attach a prefix string to them if appropriate. Prefixing allows for running multiple sites from the same database or, in limited cases, for sharing selected tables between sites.
Placeholders
Placeholders mark where a literal will be inserted into a query for execution. By separating them out from the query itself, we allow the database to differentiate between SQL syntax and user-provided values, thus avoiding SQL injection.
<?php
$result = db_query("SELECT nid, title FROM {node} WHERE created > :created", array(
':created' => REQUEST_TIME - 3600,
));
?>The above code will select all nodes created within the past hour (3600 seconds). The placeholder :created will be dynamically replaced by whatever the value of time() - 3600 is at the point the query is run. A query may have any number of placeholders, but all must have unique names even if they have the same value. Depending on the use case, the placeholders array may be specified inline (as above) or may be built beforehand and passed in. The order of the array does not matter.
Placeholders beginning with "db_" are reserved for internal system use and should never be specified explicitly.
Note that placeholders should not be escaped or quoted regardless of their type. Because they are passed to the database server separately, the server is able to differentiate between the query string and the value on its own.
<?php
// WRONG:
$result = db_query("SELECT nid, title FROM {node} WHERE type = ':type'", array(
':type' => 'page',
));
// CORRECT:
$result = db_query("SELECT nid, title FROM {node} WHERE type = :type", array(
':type' => 'page',
));
?>Placeholder arrays
Drupal's database layer includes an extra feature of placeholders. If the value passed in for a placeholder is an array, it will be automatically expanded into a comma separated list as will the corresponding placeholder. That means developers do not need to worry about counting how many placeholders they will need.
An example should make this behavior clearer:
<?php
// This code:
db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144));
// Will get turned into this prepared statement equivalent automatically:
db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
':nids_1' => 13,
':nids_2' => 42,
':nids_3' => 144,
));
// Which is equivalent to the following literal query:
db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)",
?>Query options
The third parameter to db_query() (and to the query method of the connection object) is an array of options that direct how the query will behave. There are typically only two directives that will be used by most queries. The other values are mostly for internal use.
The "target" key specifies the target to use. If not specified, it defaults to "default". At present, the only other valid value is "slave", to indicate that a query should run against a slave server if one exists.
The "fetch" key specifies how records returned from that query will be retrieved. Legal values include PDO::FETCH_OBJ, PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, or a string representing the name of a class. If a string is specified, each record will be fetched into a new object of that class. The behavior of all other values is defined by PDO, and will retrieve records as a stdClass object an associative array, or an array keyed both numerically and associatively, respectfully. See http://www.php.net/PDOStatement-fetch . The default is PDO::FETCH_OBJ, which for consistency should be used unless there is a specific reason to do otherwise.
The following example will execute a query against a slave server if available and fetch records from the result set as an associative array.
<?php
$result = db_query("SELECT nid, title FROM {node}", array(), array(
'target' => 'slave',
'fetch' => PDO::FETCH_ASSOC,
));
?>Result sets
A Select query will always return a result set object of zero or more records. There are several ways to then retrieve data from that result set, depending on the use case.
The most common case is to iterate over the result set with a foreach() loop.
<?php
$result = db_query("SELECT nid, title FROM {node}");
foreach ($result as $record) {
// Do something with each $record
}
?>Depending on what the results are needed for, however, there are a number of other ways to retrieve the records.
To explicitly fetch the next record, use:
<?php
$record = $result->fetch(); // Use the default fetch mode.
$record = $result->fetchObject(); // Fetch as a stdClass object.
$record = $result->fetchAssoc(); // Fetch as an associative array.
?>If there is no next record, FALSE will be returned. fetch() should generally be avoided in favor of fetchObject() and fetchAssoc(), as the latter are more self-documenting. If you need to use some other PDO-supported fetch mode, then use fetch().
To fetch just a single field out of the result set, use:
<?php
$record = $result->fetchField($column);
?>The default value of $column is 0, for the first field.
To fetch all records at once into a single array, use one of the following:
<?php
// Retrieve all records into an indexed array.
$result->fetchAll();
// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// Retrieve a 1-column result set as one single array.
$result->fetchCol();
?>Note that fetchAll() and fetchAllAssoc() will by default fetch using whatever fetch mode was set on the query (numeric array, associative array, or object). That can be modified by passing in a new fetch mode constant. For fetchAll(), it is the first parameter. For fetchAllAssoc(), it is the second parameter.
Because PHP supports chaining method calls on returned objects, it is very common to skip the $result variable entirely, like so:
<?php
// Get an associative array of nids to titles.
$nodes = db_query("SELECT nid, title FROM {node}")->fetchAllKeyed();
// Get a single record out of the database.
$node = db_query("SELECT * FROM {node} WHERE nid = :nid", array(':nid' => $nid))->fetchObject();
// Get a single value out of the database.
$title = db_query("SELECT title FROM {node} WHERE nid = :nid", array(':nid' => $nid))->fetchField();
?>