| 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
439 calls to db_query()
2 string references to 'db_query'
File
- includes/
database/ database.inc, line 2310 - Core systems for the database layer.
Code
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();wunderfull method, thanks for
wunderfull method, thanks for sharing
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.
Query not working
This is my select statement but i did not get desired results
$name = arg(2);
"SELECT fm.filename,fm.uri as uri,n.title FROM field_data_field_product_media pm
JOIN field_data_field_product_image_video piv ON ( pm.field_product_media_value = piv.entity_id
AND pm.delta =0 ) JOIN file_managed fm ON piv.field_product_image_video_fid = fm.fid JOIN node n
ON pm.entity_id = n.nid JOIN field_data_field_categories fc ON fc.entity_id = n.nid
JOIN taxonomy_term_data td ON td.tid = fc.field_categories_tid WHERE td.name = ':name'", array(':name' => $name);
It's hard to debug without
It's hard to debug without more information (aka what you mean by "desired results"). But I got into some problem porting from D6. It seems that you need to remove the quote around :name in your query.
@see: http://api.drupal.org/api/drupal/includes%21database%21database.inc/func...
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.
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:
<?php$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.
What's the best way to replace db_fetch_array(db_query() ?
I have queries that I *know* will only return one row (or nothing) - that is, my WHERE is on the primary key column. Just to be safe, I have always also included a LIMIT 1, but that's not really necessary (and I am removing that in D7).
Can someone tell me the syntax for replacing something like this (from D6) in D7?...
$my_array = db_fetch_array(db_query('SELECT * FROM {my_table} WHERE my_unique_key = %d', $my_unique_key_value));check if email already exists in DB
Hi,
I am trying to check if an email entered by the user is already in the database.
I use this code, where $courrier is the email variable:
<?phpif (!db_query("SELECT COUNT(*) FROM {users} WHERE mail = : $courrier ", array(':name' => $courrier))->fetchField()) {
echo ("Email doesn't exist");
}
?>
I have this PDO error:
What is wrong with my SQL Syntax here ?
<?phpif (!db_query("SELECT
<?phpif (!db_query("SELECT COUNT(*) FROM {users} WHERE mail = :name", array(':name' => $courrier))->fetchField()) {
echo ("Email doesn't exist");
}
?>