The most common SELECT queries in Backdrop are static queries using the db_query() function. Static queries are passed to the database nearly verbatim, as in the following example.
$query = db_query("SELECT nid, title FROM {node}");
$records = $query->fetchAll();
foreach ($records as $record) {
// Do something.
}Only very simple SELECT queries should use the static query mechanism. You should use a dynamic query if you need more complex queries, dynamic query generation or alterability.
Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.
db_query() takes three arguments:
- $query: the query to run. Use placeholders where appropriate and denote all table names with curly braces.
- $args: an array of placeholder values to substitute into the query.
- $options: an array of options to control how the query operates (optional).
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.
$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 REQUEST_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.
// WRONG (quotes around the :type placeholder)
$result = db_query("SELECT title FROM {node} WHERE type = ':type'", array(
':type' => 'page',
));
// CORRECT: (no quotes around the :type placeholder)
$result = db_query("SELECT title FROM {node} WHERE type = :type", array(
':type' => 'page',
));Placeholders cannot be used for column and table names. Instead, if these are derived from unsafe input, they should be run through db_escape_table().
Placeholder arrays
Backdrop'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 don't need to worry about counting how many placeholders they will need.
db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144)));That code is equivalent to the following code.
db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
':nids_1' => 13,
':nids_2' => 42,
':nids_3' => 144,
));
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, a numerical array, or an array keyed both numerically and associatively, respectively. See http://php.net/manual/en/pdostatement.fetch.php. 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.
$result = db_query("SELECT nid, title FROM {node}", array(), array(
'target' => 'slave',
'fetch' => PDO::FETCH_ASSOC,
));Using the result
The result object returned by a call to db_query() can be used to get each of the rows returned and then columns. In the following example, the $result variable has all the rows of the query returned, and then the individual rows are pulled one at a time into the $row variable using fetchAssoc():
$sql = "SELECT name, quantity FROM {goods} WHERE vid = :vid";
$result = db_query($sql, array(':vid' => $vid));
if ($result) {
while ($row = $result->fetchAssoc()) {
// Do something with:
// $row['name']
// $row['quantity']
}
}