| 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()) |
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()
1 string reference to 'db_query'
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
Must use arguments to store serialized data
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)
);
db_query("INSERT...") is discouraged
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?
drupal_write_record() only inserts one data object, though
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?
Drupal 7 overhauls the Database API
In D7 db_insert supports multiple insertion
ww9rivers is correct. If
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
Yes, in that case use db_query. It's discouraged but not deprecated, and may be used where appropriate ;)
I think you would prefer
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))
);
Placeholder | Meaning
Placeholder | Meaning
%s | String
%d | Integer
%b | Binary
%% | Inserts a literal % sign
%f | Float
sprintf function
Which most likely use or is based on sprintf().
date data assignment?
how about the date data type?
can you explain about the date?
with IN()
Here's an example how to use
db_query()properly withIN(). 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);
?>
db_placeholders
<?php$tids; // An array of term IDs
$placeholders = implode(', ', array_fill(0, count($tids), "%d"));
?>
Thats exactly what db_placeholders() does...
db_placeholders does not exist in D7
nt
A word of caution
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=''
This works for me
<?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
However wondered how to print the results?
Here is a post that provides some examples how to print the result of your query:
http://drupal.org/node/567786
Fun and easy
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);
?>
Checking result
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.
Dereferencing $result
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>';
}
?>
PostgreSQL query compatibility
In PostgreSQL queries, %s have to be enclosed in apostrophes ( ' ), quotation marks ( " ) will not work.
If a query that has % in
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.
Saved my day!! yay!
Saved my day!! yay!
Thanks! It is easy to miss
Thanks!
It is easy to miss the %b.
There is no
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.
Simple Example
A missing simple example
<?phpdb_query("SELECT * FROM {node} WHERE nid=%d AND title = '%s', $nid, $title");
?>
simple example mistake...
The previous example will not work, the right example would be:
<?phpdb_query("SELECT * FROM {node} WHERE nid=%d AND title = '%s'", $nid, $title);
?>
It doesn't work at all
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).
when using db_query()
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...
<?phpdb_query("SELECT * FROM {node} WHERE nid=%d AND title = '%s'", $nid, $title);
?>
hope this helps.
WIldcards...
So how can you correctly use a simple wildcard like this within a sql query for Drupal?
SELECT name Nombre, mail Email
FROM users
WHERE status = 1
AND mail LIKE ('%@hotmail%')
LIMIT 25
Cause I am getting this error:
Division by zero in /home/mysite/www2/dev/sites/all/modules/onthisdate/onthisdate.module in line 38.
try this
LIKE '%%@hotmail%%'