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

Runs a basic query in the active database.

User-supplied arguments to the query should be passed in as separate parameters so that they can be properly escaped to avoid SQL injection attacks.

Parameters

$query: A string containing an SQL query.

...: A variable number of arguments which are substituted into the query using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments.

Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose in '') and %%.

NOTE: using this syntax will cast NULL and FALSE values to decimal 0, and TRUE values to decimal 1.

Return value

A database query result resource, or FALSE if the query was not executed correctly.

Related topics

418 calls to db_query()
actions_delete in includes/actions.inc
Delete a single action from the database.
actions_do in includes/actions.inc
Perform a given list of actions by executing their callback functions.
actions_function_lookup in includes/actions.inc
Given an md5 hash of a function name, return the function name.
actions_get_all_actions in includes/actions.inc
Retrieves all action instances from the database.
actions_load in includes/actions.inc
Retrieve a single action from the database.

... See full list

1 string reference to 'db_query'
drupal_error_handler in includes/common.inc
Log errors as defined by administrator.

File

includes/database.pgsql.inc, line 115
Database interface code for PostgreSQL database servers.

Code

function db_query($query) {
  $args = func_get_args();
  array_shift($args);
  $query = db_prefix_tables($query);
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  return _db_query($query);
}

Comments

Note that to store serialized data, it must be passed as an argument or it will be corrupted by db_query. The following example shows how to store serialized arrays:

  $result = db_query("INSERT INTO {my_table}
                        (name, description, array1, array2)
                        VALUES ('something', 'something else', '%s', '%s')",
                       serialize($myarray1), serialize($myarray2)
                      );

As of Drupal 6, data can be inserted into the database using drupal_write_record(), and the fields can be noted as "'serialize' => true" when declared in hook_schema(). drupal_write_record() will then serialize your data for you and everything.

<?php
  $data
= array(
   
'name' => 'something',
   
'description' => 'something else',
   
'array1' => $myarray1,
   
'array2' => $myarray2
 
);
 
drupal_write_record('my_table', $data);
?>

Would you like to know more?

But there doesn't seem to be a way to do insert of multiple value, for example:

  INSERT INTO `table` (`id`,`value`) VALUES (1, 'one'), (2, 'two');

I guess it isn't be too hard for Drupal to provide an API function for that. Maybe not enough demand?

I guess it isn't be too hard for Drupal to provide an API function for that. Maybe not enough demand

In D7 db_insert supports multiple insertion

ww9rivers is correct. If you're inserting using large volumes (100 or more) in short form syntax, then use db_query, not drupal_write_record.
DR6 drupal_write_record does validation per record, then individual inserts (does DR7 do the same? the documentation is out of date if it handles multiples) effectively losing the bandwidth, memory and speed advantages of multiple inserts per sql expression.

Yes, in that case use db_query. It's discouraged but not deprecated, and may be used where appropriate ;)

I think you would prefer wrapping all your arguments in the same array, according to the function signature :

$result = db_query("INSERT INTO {my_table}
                        (name, description, array1, array2)
                        VALUES ('something', 'something else', '%s', '%s')",
                       array(serialize($myarray1), serialize($myarray2))
                      );

Here's an example how to use db_query() properly with IN(). I spent some time looking for this information, so maybe I can save someone else the time.

Selecting nodes with a certain set of terms from {term_node}:

<?php
$tids
// An array of term IDs
$placeholders = implode(', ', array_fill(0, count($tids), "%d"));
$r = db_query("SELECT nid FROM {term_node} WHERE tid IN ($placeholders)", $tids);
?>

<?php
$tids
// An array of term IDs
$placeholders = implode(', ', array_fill(0, count($tids), "%d"));
?>

Thats exactly what db_placeholders() does...

nt

This has gotten me twice and its a pain to debug. If your second argument to the function is an array, all following arguments are ignored and not used as expected in the query. See the lines around the comment // 'All arguments in one array' syntax. If you must pass an array of arguments make sure it has ALL your arguments. This means you can't do a query like the following:

<?php
$tids
// An array of term IDs
$type; // string identifying a content type.
$placeholders = implode(', ', array_fill(0, count($tids), "%d"));
$r = db_query("SELECT nid FROM {term_node} WHERE tid IN ($placeholders) and type='%s'", $tids, $type);
?>

If you use db_queryd from the devel module, the sql statement generated will be:
SELECT nid from term_node WHERE tid in (1,2,3,4,5) and type=''

<?php
$tids
// An array of term IDs
$type; // string identifying a content type.
$args = array_merge($tids, array($type));

$r = db_query("SELECT nid FROM {term_node} WHERE tid IN (".db_placeholders($tids, 'int').") AND type='%s'", $args);
?>

Hope that helps! took hours to find a fix

Here is a post that provides some examples how to print the result of your query:

http://drupal.org/node/567786

Combine db_fetch_array and theme_table for fun and profit!

<?php
$q
= db_query($query);
while (
$r = db_fetch_array($q)) {
 
$rows[] = $r;
}
print
theme('table', $header, $rows);
?>

This is the simplest example - you can have even more fun by adding a $header array with references to the fields in your table, and appending your $query with the result of tablesort_sql($header)...

<?php
$query
= "SELECT field_1, field_2, field_3 FROM {mytable}";
$header = array(
  array(
'data' => t('Cell 1'), 'field' => 'field_1', 'sort' => 'ASC'),
  array(
'data' => t('Cell 2'), 'field' => 'field_2'),
  array(
'data' => t('Cell 3'), 'field' => 'field_3'),
);
$q = db_query($query . tablesort_sql($header));
while (
$r = db_fetch_array($q)) {
 
$rows[] = $r;
}
print
theme('table', $header, $rows);
?>

Be aware that the result that comes back can be empty so to ensure that there is at least one entry, you can do this:

<?php
  $query
= "SELECT...";
 
$result = db_query($query);
  if (
count($result) > 0) {
   
$data = db_fetch_array($result);

    if (

$data != FALSE) {
       
// there is at least one result data
   
}
  }
?>

Note, $result is an object, not an array and so you cannot use PHP's empty() function to check to see if it is empty.

You can't depend on count($result) getting what you want. $result will be a different type depending on the database backend you're using. Only use the functions listed in the database API on $result.

If you need to find the number of items returned by a query, use the SQL COUNT() function.

If you need to find out if you got any data back at all, check if the return value of db_fetch_array($result), db_result($result), or db_fetch_object($result) is FALSE.

<?php
$result
= db_query('SELECT a, b, c FROM d WHERE e > 25');
$no_results = TRUE;
while (
$row = db_fetch_array($result)) {
 
$no_results = FALSE;
 
$output .= 'a = ' . $row['a'] . ', b = ' . $row['b'] . ' , and c = ' . $row['c'] . '<br />';
}

if (

$no_results) {
 
$output = '<p>' . t('No results found for e greater than 25!') . '</p>';
}
?>

In PostgreSQL queries, %s have to be enclosed in apostrophes ( ' ), quotation marks ( " ) will not work.

If a query that has % in them, like in a mysql DATEFORMAT(%Y-%d-%m). You need to add a second % like this DATEFORMAT(%%Y-%%d-%%m) the % needs to be escaped so that db_query doesn't try to replace it.

There is no documentation/example for $options. For each API function documentation, adding one example that covers all the parameters will be of great help.

A missing simple example

<?php
  db_query
("SELECT * FROM {node} WHERE‌ nid=%d AND title = '%s', $nid, $title");
?>

The previous example will not work, the right example would be:

<?php
db_query
("SELECT * FROM {node} WHERE nid=%d AND title = '%s'", $nid, $title);
?>

here what I need:

$activation = '1';
$query = db_query("SELECT SUM(active_payments) FROM  payments WHERE  uid = %d AND  verified = '%s'" , $what_user, $activation);

Here what it tells:

Recoverable fatal error: Argument 2 passed to db_query() must be an array, string given, called in /web/htdocs/www.mysite.com/home/modules/php/php.module(74) : eval()'d code on line 180 and defined in db_query() (line 2309 of /web/htdocs/www.mysite.com/home/includes/database/database.inc).

Using db_query() is a good function for database handling thus it already have been altered to make a compatibility on different database.

db_query() automatically escapes operation that are concatenated at the string arguments and causes to produce unwanted result so database query will show an error.

here's a sample code:

<?php
$foo
= 100;
db_query("SELECT * FROM `bar` WHERE foo = `" . $foo + 1 . ");
//This would produce error in the query.
?>

The proper way around would be:

<?php
$foo
= 100;
$added_foo = $foo + 1;
db_query("SELECT * FROM `bar` WHERE foo = `" . $added_foo + 1 . ");
?>

or like what garen24 have said...

<?php
db_query
("SELECT * FROM {node} WHERE nid=%d AND title = '%s'", $nid, $title);
?>

hope this helps.

The syntax for the placeholders is based on sprintf. See the _db_query_callback() documentation.