function db_query

You are here

7 database.inc db_query($query, array $args = array(), array $options = array())
4.6 database.inc db_query($query)
4.7 database.inc db_query($query)
5 database.inc db_query($query)
6 database.pgsql.inc db_query($query)
6 database.mysql-common.inc db_query($query)
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

449 calls to db_query()
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/actions.inc
Performs a given list of actions by executing their callback functions.
actions_function_lookup in includes/actions.inc
Returns an action array key (function or ID), given its hash.
actions_get_all_actions in includes/actions.inc
Retrieves all action instances from the database.

... See full list

3 string references to 'db_query'
DatabaseLoggingTestCase::testEnableLogging in modules/simpletest/tests/database_test.test
Test that we can log the existence of a query.
drupal_get_filename in includes/bootstrap.inc
Returns and optionally sets the filename for a system resource.
_drupal_decode_exception in includes/errors.inc
Decodes an exception and retrieves the correct caller.

File

includes/database/database.inc, line 2345
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

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 Drupal 7 example is incomplete. It is missing a fetch command.

You can use fetchField() to get a single value. Example:

<?php
$node_title
= db_query('SELECT title FROM {node} WHERE nid = :nid', array(':nid' => $nid))->fetchField();
?>

In fact, this is very important!

<?php
// Notice the place holders are now done using the same syntax as PDOs (:uid)
// Placeholders also don't need to be quoted anymore.
?>

Use :my_var instead of ':my_var' (without the '') or else the placeholder will not be replaced when the query is run.

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();

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 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.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
    LIMIT 1
  ';

WORKS:

$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
    LIMIT 1
  ';

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 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.

This fails:

<?php
  db_query
("SELECT nid FROM node LIMIT :d",array(":d"=>3));
?>

Use this instead:

<?php
   db_query_range
("SELECT nid FROM node",0,3);
?>

You can use this:

<?php
  db_query
("SELECT nid FROM node ")->range(0, 3);
?>

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.

use implode(',', $my_ids) or if you're using 6: db_placeholders


$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 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');

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.

Thanks.

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.

http://api.drupal.org/api/drupal/includes!database!database.inc/function...
From the example in the documentation:

<?php
$result
= db_query(
 
'SELECT * FROM person WHERE name LIKE :pattern',
  array(
':pattern' => db_like($prefix) . '%')
);
?>

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.

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.

Main Database API docs are here: http://drupal.org/node/310069

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.

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

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.