text:
|8.3.x database.inc
db_query($query, array $args = [], array $options = [])
|8.0.x database.inc
db_query($query, array $args = array(), array $options = array())
|8.1.x database.inc
db_query($query, array $args = array(), array $options = array())
|8.2.x database.inc
db_query($query, array $args = array(), array $options = array())
|8.4.x database.inc
db_query($query, array
|4.6.x database.inc
|4.7.x database.inc
|5.x database.inc
|6.x database.pgsql.inc
|6.x database.mysql-common.inc
|7.x database.inc
db_query($query, array = array(), array = 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.
$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
Related topics
- ActionLoopTestCase::triggerActions in modules/
simpletest/ tests/ actions.test
- Create an infinite loop by causing a watchdog message to be set, which causes the actions to be triggered again, up to actions_max_stack times.
- ActionsConfigurationTestCase::testActionConfiguration in modules/
simpletest/ tests/ actions.test
- Test the configuration of advanced actions through the administration interface.
- actions_do in includes/
- Performs a given list of actions by executing their callback functions.
- actions_function_lookup in includes/
- Returns an action array key (function or ID), given its hash.
- actions_get_all_actions in includes/
- Retrieves all action instances from the database.
- DatabaseLoggingTestCase::testEnableLogging in modules/
simpletest/ tests/ database_test.test
- Test that we can log the existence of a query.
- drupal_get_filename in includes/
- Returns and optionally sets the filename for a system resource.
- _drupal_decode_exception in includes/
- Decodes an exception and retrieves the correct caller.
- includes/
database/ database.inc, line 2401
- Core systems for the database layer.
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);
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
// 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)
// 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.
// 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)
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
// 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.
// Column number can be specified otherwise defaults to first column
// Count the number of rows
Please note that the above
Please note that the above Drupal 7 example is incomplete. It is missing a fetch command.
You can use fetchField() to
You can use fetchField() to get a single value. Example:
$node_title = db_query('SELECT title FROM {node} WHERE nid = :nid', array(':nid' => $nid))->fetchField();
Don't use '' around placeholder
In fact, this is very important!
// Notice the place holders are now done using the same syntax as PDOs (:uid)
// Placeholders also don't need to be quoted anymore.
:my_var instead of
':my_var' (without the '') or else the placeholder will not be replaced when the query is run.
The above example is rather
The above example is rather confusing because $result isn't the result of the query, $result is the query itself. It's more clear written this way, replacing $result with $query and using $result as the array with the actual results of the query:
$query = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));
$result = $query->fetchAll();
foreach ($result as $record) {
@WillGFP : You can iterate
@WillGFP : You can iterate over the result object. Why add the extra step and not include a reason for doing it? Your example is actually more confusing than the parent.
WillGFP is technically correct
The result of the db_query is of type $DatabaseStatementBase, which extends PDOStatement, both of which are closer to a query than to the result of the query. And though the result is in that object, WillGFP's recommendation is technically more correct because we are looking at a query object that also now contains its result.
I hadn't really noticed that before and I'm going to take his recommendation.
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.
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:
$sql = 'SELECT n.nid
FROM 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
$sql = 'SELECT n.nid
FROM 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
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.
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:
db_query("SELECT nid FROM node LIMIT :d",array(":d"=>3));
Use this instead:
db_query_range("SELECT nid FROM node",0,3);
You can use this:
You can use this:
db_query("SELECT nid FROM node ")->range(0, 3);
Doesn't work in Drupal 7.
Doesn't work in Drupal 7.
Confirmed not working in D7
'LIMIT' cannot be used inside the SQL statement and range can't be chained so use db_query_range instead and save yourself an hour or so of hassle!
Not working in D7
Not working in D7
error: Call to undefined method DatabaseStatementBase::range() ...............
Placeholders expect array when using IN()
I had a query like this
$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.
not working, even with 'implode'
$comma_separated = implode(",", $fids);
echo $comma_separated; // echoes 21,22,23
$r = db_query("SELECT DISTINCT field_product_downloads_description FROM {field_data_field_product_downloads} WHERE field_product_downloads_fid IN (:fids)", array(':fids' => $comma_separated));
only returns the value(s) for the first number "21"
I had the same problem
I reread the comment and now it's clear You have to use an array inside the paramters array.
$param = array(':nids' => array('2342','2343','2341'),':uid'=>'234');
A working example
The comments above are a bit confusing. You simply need to make sure you wrap the placeholder in parentheses and then pass in the array of values in your placeholder replacement. Here's a working example:
$nids = array(100, 101);
$records = db_query("SELECT nid, title FROM node WHERE nid IN (:nids)", array(':nids' => $nids))->fetchAll();
$records is then an array of objects with these 2 properties:
$records = array(
0 => stdClass(
nid => 100,
title => 'some title,
1 => stdClass(
nid => 101,
title => 'another title,
I just wanted to thank for your clear response. I was trying to retrieve a set of data using the WHERE IN clause, wasn't working because I was using the implode function to create a variable like this: "123,456,321,563"
I changed the string variable to an array and worked well.
String based searches
Incase anyone else has trouble on a basic query like I did and has read this far down this thread.
If you are making a LIKE or string based query do this
db_query("SELECT entity_id FROM {field_data_field_st_series_description} WHERE field_st_series_description_value LIKE :series_description", array(':series_description'=>$node->field_show_times['und'][0]['series_description']));
Leaving the quotes out between LIKE ':series_description'
I am use to wrapping stings in quotes for queries and this was something my skimming of documentation noticed.
db_like is the way to go
From the example in the documentation:
$result = db_query(
'SELECT * FROM person WHERE name LIKE :pattern',
array(':pattern' => db_like($prefix) . '%')
With Drupal 7.14 update, case are preserved
Prior to 7.14, key names for results were lowercased, for example column name Code became code. Now db_query will preserve case, Code will be Code.
Target option
The target option is typically either "default" or "slave", indicating to use a slave SQL server if one is available.
To query another database, do:
$select = Database::getConnection('default', 'other_db')->query('tablename', 'alias');
Make sure your db settings in settings.php file contains the other database (other_db). See description of settings.php on how to define other databases.
Main Database API docs are
Regex repetition
Someone mentioned it in the D6 documentation for this function and it bears reiterating here:
If you have a query as follows:
db_query("SELECT * FROM {node} WHERE `title` REGEXP('[0-9]{4}-[0-9]{2}-[0-9]{2}')");
(I.e., title is a date like 2012-10-29)
...The repetition bits will get removed and the regex will look like "[0-9]4-[0-9]2-[0-9]2"
Instead, use the substitution array $arg:
db_query("SELECT * FROM {node} WHERE `title` REGEXP(:regex)", array(':regex' => '[0-9]{4}-[0-9]{2}-[0-9]{2}') );
Again, this differs from D6 in that it's an array with PDO-style (:something) and not simply print_f-style (%s) replacement.
db_query() is faster and should be used in 90% of cases
Note: in 90% of select query use cases you will have a static query. If in a critical performance path, you should use db_query() and friends instead of db_select() for performance reasons.
Source: http://drupal.org/node/310075
See https://www.drupal.org
Different Results Between db_query and direct MySQL
I learned the hard way that MySQL accepts || as 'or' whereas db_query accepts it but treats it differently and yields different query results. My query was something like "select x, y from z where (x = 'somevalue' || y = 'somevalue')". With db_query, it returned all rows in the table.
not the same in .install
Not sure what is going on here but in an .install update hook i am trying to copy records from one table to another and i do this:
db_query("INSERT field_data_field_links_user SELECT * FROM field_data_field_links WHERE entity_type = 'user'")->execute();
db_query("INSERT field_revision_field_links_user SELECT * FROM field_revision_field_links WHERE entity_type = 'user'")->execute();
the first line runs through all the records and then for some reason tries to do the inserts again and gets a PDO exception due to primary key violation. so the 2nd line never gets hit.
when i run this directly in a devel/php window it works fine.
nope, my bad.. it fails in
nope, my bad.. it fails in devel/php as well
db_query to create stored proc in mysql shows double quotes
I have sql file that i am calling in the .install file using the db_query option. the sql file has a CREATE PROCEDURE slp_validate_user() stored proc. when the install file creates the stored proc, it creates the stored proc with a double quotes in the name CREATE PROCEDURE "slp_validate_user"() . due to this when i call the stored proc, in my php code i get "Error: Unknown column '' in 'field list' " error. can someone help me on this issue ? should i not use db_query to create stored proc in the install file ?
Date format in query
If you have date format function in field or condition in db_query like below:
$sql = 'SELECT (DATE_FORMAT(FROM_UNIXTIME(co.changed), "%d-%m-%Y")) AS date, ci.type AS type, co.status, COUNT(distinct(co.order_id)) AS count
FROM commerce_order co
LEFT JOIN commerce_line_item ci ON co.order_id = ci.order_id
GROUP BY date, co.status, ci.type
ORDER BY date';
$result = db_query($sql);
foreach ($result as $row) {
fails like this:
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '%d-%m-%Y' in 'field list': SELECT DATE_FORMAT(FROM_UNIXTIME(co.changed), "%d-%m-%Y") AS date, ci.type AS type, co.status, COUNT(distinct(co.order_id)) AS count FROM commerce_order co LEFT JOIN commerce_line_item ci ON co.order_id = ci.order_id GROUP BY date, co.status, ci.type ORDER BY date;; Array ( )
To avoid this, we had to use double % or pass format as argument as mentioned in http://i-heart-geek.blogspot.co.uk/2011/09/drupal-dbquery-and-date-formats.html
However, looks like D7 core has been fixed to support this without the above workaround. Only condition is the order of single & double quotes. So below query works fine:
$sql = "SELECT (DATE_FORMAT(FROM_UNIXTIME(co.changed), '%d-%m-%Y')) AS date, ci.type AS type, co.status, COUNT(distinct(co.order_id)) AS count
FROM commerce_order co
LEFT JOIN commerce_line_item ci ON co.order_id = ci.order_id
GROUP BY date, co.status, ci.type
ORDER BY date";
$result = db_query($sql);
foreach ($result as $row) {
Note that the single quotes inside double quotes.
How do i use db_query to
How do i use db_query to execute stored procedure
Looking for a hand; apologies
Looking for a hand; apologies if this is already laid out somewhere, but I can't seem to find any information.
Is it possible with db_query to use the and operator to search on multiple columns? an example of what I'm trying to do is below. Thanks in advance.
$songID = db_query("SELECT nid FROM {node} WHERE type = :type AND author = :author order by RAND() LIMIT 1", array( ':type' => 'book', ':author' => 'J.R.R. Tolkien',));
// make more efficient? (Just one result)
while($record = $songID->fetchAssoc()) {
$mySongID = $record["nid"];
Yes, your code looks ok.
Yes, your code looks ok.
query with multiple conditions
Thanks Nevets for getting back to me;
so what I was looking for (sorry I didn't ask more clearly) was how to use db_query with multiple operands.
I'm still fairly new with the ways of drupal, and how to use it, but I ended up figuring this out. I'm posting this, in hopes that maybe it will be helpful to someone else. In all the db_query examples I've found, they only have one operand.
This example will find all nodes, where the user id is equal to 1, and the type is equal to a page.
It then iterates through the resuls, and parses each result on a new line.
// 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 AND n.type = :type', array(':uid' => $uid, ':type' => 'page'));
// 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.
print($record->title . "
// in this example the available data would be mapped to object properties:
// $record->nid, $record->title, $record->created
Is there a reson for single quotes
Hi im curios about all the exampels alawys using single quots.
I find i always have to rewrite with double quotes as at least the version of mysql i'm using only takes singles quotes inside the query.
"SELECT n.title FROM {node} n WHERE .title = 'TEST'";
Placeholders usage is ... mandatory
Your query has to be sanitized using placeholders:
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid AND n.type = :type', array(':uid' => $uid, ':type' => 'page'));
so it should not contains any value that have to be wrapped in quotes. So the examples are good and your queries may be bad if you have to provide your values inside the query to db_query.