| 5 database.inc | db_query($query) |
| 6 database.mysql-common.inc | db_query($query) |
| 6 database.pgsql.inc | db_query($query) |
| 7 database.inc | db_query($query, array $args = array(), array $options = array()) |
| 8 database.inc | db_query($query, array $args = array(), array $options = array()) |
Executes an arbitrary query string against the active database.
Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.
Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.
Parameters
$query: The prepared statement query to run. Although it will accept both named and unnamed placeholders, named placeholders are strongly preferred as they are more self-documenting.
$args: An array of values to substitute into the query. If the query uses named placeholders, this is an associative array in any order. If the query uses unnamed placeholders (?), this is an indexed array and the order must match the order of placeholders in the query string.
$options: An array of options to control how the query operates.
Return value
DatabaseStatementInterface A prepared statement object, already executed.
See also
DatabaseConnection::defaultOptions()
Related topics
436 functions call db_query()
File
- includes/
database/ database.inc, line 2313 - Core systems for the database layer.
Code
<?php
function db_query($query, array $args = array(), array $options = array()) {
if (empty($options['target'])) {
$options['target'] = 'default';
}
return Database::getConnection($options['target'])->query($query, $args, $options);
}
?>
Comments
Example #1
An simple example borrowed from http://api.drupal.org/api/group/database/7 check it out for a good overview of the Database abstraction layer. Even better, checkout the Database API documentation at http://drupal.org/node/310072 for more in depth coverage
<?php
// Drupal 7
// Notice the place holders are now done using the same syntax as PDOs (:uid)
// Placeholders also don't need to be quoted anymore.
$uid = 1;
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));
// Result is returned as a iterable object that returns a stdClass object on each iteration
foreach ($result as $record) {
// Perform operations on $record->title, etc. here.
// in this example the available data would be mapped to object properties:
// $record->nid, $record->title, $record->created
}
// Same example in Drupal 6
$uid = 1;
$result = db_query("SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = %d", $uid);
while ($record = db_fetch_object($result)) {
// Perform operations on $record->title, etc. here.
}
// NOTE: db_fetch_object and db_fetch_array have been removed from D7!
?>
It's also good to know that the drupal Database Abstraction layer is built on top of the PHP Database Object (PDO) extension and uses much of the syntax defined there for accessing the database. For a good base understanding of where this new abstraction later is coming from, I recommend checking out the PDO documentation on php.net specifically check out the PDOStatement class. A PDOStatement object is not that far off from what is returned from:
db_query()And drupal has used some of those functions (e.g. PDOStatment::fetchObject()), as well as added some new ones, to the Database API. Here are some quick examples (borrowed from the Database API documentation)<?php
// Using the same query from above...
$uid = 1;
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));
// Fetch next row as a stdClass object.
$record = $result->fetchObject();
// Fetch next row as an associative array.
$record = $result->fetchAssoc();
// Fetch data from specific column from next row
// Defaults to first column if not specified as argument
$data = $result->fetchColumn(1); // Grabs the title from the next row
// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();
// Retrieve all records as stdObjects into an associative array
// keyed by the field in the result specified.
// (in this example, the title of the node)
$result->fetchAllAssoc('title');
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// Also good to note that you can specify which two fields to use
// by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be nid => created
$result->fetchAllKeyed(1,0); // would be title => nid
// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($db_column_number);
// Count the number of rows
$result->rowCount();
?>
Please note that the above
Please note that the above Drupal 7 example is incomplete. It is missing a fetch command.
Getting a Single Value
Example from «Pro Drupal 7 development book (3rd edition)»:
If all you need from the database is a single value, you can use the ->fetchField() method to retrieve that value. Here is an example of retrieving the total number of records from the joke table:
$nbr_records = db_query("SELECT count(nid) FROM {joke}")->fetchField();You must use single quotes
This is new in Drupal 7. Although MySQL doesn't care about single or double quotes, other SQL engines do.
Works:
SELECT delta FROM {block} WHERE status = 0 AND module = 'tracker'Does not work:
SELECT delta FROM {block} WHERE status = 0 AND module = "tracker"This killed me so hard. I
This killed me so hard. I spent an hour bashing my head against queries that worked perfectly when run directly against the db but failed in db_query. I've done tons of work in d6, but this is my first d7 site.
If you're getting
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'xxxxx' ...where xxxxx is a value and was never supposed to be a friggin' column name to begin with, check to make sure you're wrapping string values in single quotes.
My more complicated example:
FAILS:
$sql = 'SELECT n.nidFROM field_data_field_run_date rd
INNER JOIN node n ON rd.entity_id = n.nid
WHERE STR_TO_DATE(rd.field_run_date_value, "%Y-%m-%dT%T") <= NOW()
AND n.type = "potd"
AND n.status = 1
ORDER BY rd.field_run_date_value DESC, n.created DESC
LIMIT 1
';
WORKS:
$sql = 'SELECT n.nidFROM field_data_field_run_date rd
INNER JOIN node n ON rd.entity_id = n.nid
WHERE STR_TO_DATE(rd.field_run_date_value, \'%Y-%m-%dT%T\') <= NOW()
AND n.type = \'potd\'
AND n.status = 1
ORDER BY rd.field_run_date_value DESC, n.created DESC
LIMIT 1
';
The reason your first query
The reason your first query fails, is that you're trying to use double quotes (or "").
If you know quotes are going to be used in a query, open and close your statement with double quotes and wrap any VALUES in the statement that require quotes with a single quote.
It appears that if you use
It appears that if you use query strings that span multiple lines, the named arguments don't appear to work. Removing newlines from the query string solves it. Anyone else experiencing this issue?
Multilines work fine
I'm using query string of as many as 20 lines without a problem.
On another note, the named variables should NOT be put between single quotes:
Wrong:
...WHERE node.type = ':named-argument' ...Right:
...WHERE node.type = :named-argument ...Just a note that doesn't
Just a note that doesn't appear to be documented:
If your table contains column names with uppercase letters, they will be converted to lowercase in the resulting stdClass object you get when iterating through the result object db_query() returns to you.
Placeholders do not work with LIMIT
This fails:
<?phpdb_query("SELECT nid FROM node LIMIT :d",array(":d"=>3));
?>
Use this instead:
<?phpdb_query_range("SELECT nid FROM node",0,3);
?>
Placeholders expect array when using IN()
I had a query like this
<?php$result = db_query("SELECT entity_id, some_col from {my_table} WHERE some_col IN (:my_ids)", array(':my_ids'=>$my_ids));
?>
When $my_ids where a comma separated string of numbers, I got only 4 results instead of the expected 13 rows. I passed in an array of values, all my expected results came up.
implode
use implode(',', $my_ids) or if you're using 6: db_placeholders
drupal 7 db_select tablename keyword
$results = db_select('order', 'o')
->extend('TableSort')
->orderByHeader($header)
->fields ('o', array (
'team_id',
'sku',
'pay_time',
'state',
'money',
'order_num',
))
->condition('team_id', (int) $team_id)
->execute()->fetchAll();
have something wrong , but this:
$results = db_select('order1', 'o')
->extend('TableSort')
->orderByHeader($header)
->fields ('o', array (
'team_id',
'sku',
'pay_time',
'state',
'money',
'order_num',
))
->condition('team_id', (int) $team_id)
->execute()->fetchAll();
and I changed my tablename from order to order1
that is OK.
I know that is db keyword , but how I can use orderByHeader in D7 and don't edit my db tablename.
I am from China , my English is poor.