38

I can't figure out how to add an IN clause in my query, using placeholders.

I'd want it to be something like:

$nids = array(1, 2, 3);
$result = db_query('SELECT * FROM {node} WHERE nid IN :nids', array(':nids' => $nids));

Can't find any documentation on this simple task. What's the proper way to achieve this?

47

You are missing the braces.

Try this:

$nids = array(1, 2, 3);
$result = db_query('SELECT * FROM {node} WHERE nid IN (:nids)', array(':nids' => $nids));

For more information, see http://drupal.org/node/310072, especially the chapter on Placeholder arrays:

Placeholder arrays

Drupal's database layer includes an extra feature of placeholders. If the value passed in for a placeholder is an array, it will be automatically expanded into a comma separated list as will the corresponding placeholder. That means developers do not need to worry about counting how many placeholders they will need.

An example should make this behavior clearer:

<?php
// This code:
db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144));

// Will get turned into this prepared statement equivalent automatically:
db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
  ':nids_1' => 13, 
  ':nids_2' => 42, 
  ':nids_3' => 144,
));

// Which is equivalent to the following literal query:
db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)");
?>
3
  • I knew I was close ;) Thanks for the answer and the link! – Olof Johansson Jun 16 '11 at 15:20
  • what about the strings? node_types = array('node_type_1', 'node_type_2'); – Serjas Feb 8 '13 at 13:24
  • Same, doesn't matter. – Berdir Feb 8 '13 at 15:43
19

Drupal 8

Use the entity query class.

$query = \Drupal::entityTypeManager()->getStorage('entity_type')->getQuery();
$query->condition('field/property', [1, 2, 3], 'IN');
$ids = $query->execute();

To directly query the entity database table, the code is essentially the same for other database tables.

$query = \Drupal::database()->select('table', 't');
$query->condition('column', [1, 2, 3], 'IN');
...

Drupal 7

See Berdir's answer.

Drupal 6

You can use code similar to the following one.

$nids = array(1, 2, 3);
$placeholders = db_placeholders($nids);
$result = db_query("SELECT * FROM {node} WHERE nid IN ($placeholders)", $nids);

db_placeholders() is needed in Drupal 6 to create the string that holds the values needed for the array. Drupal 7 handles all this internally as Berdir describes.

0
12

In Drupal 7, you can use db_select() instead of db_query().

$nids = array(1, 2, 3);
$query = db_select('node', 'n')
  ->fields('n')
  ->condition('n.nid', $nids, 'IN')
  ->execute();
$nodes = $query->fetchAll();
1

In Drupal 6, if your array contains strings, you need to tell db_placeholders().

$colours = array('red', 'yellow', 'blue');
$placeholders = db_placeholders($colours,'text');
$result = db_query("SELECT * FROM {bricks} WHERE colour IN ($placeholders)", $colours);
-1

Drupal 8 update.

Also valid.

$nids = db_query("SELECT nid FROM node_field_data WHERE nid IN (:nids[]) AND status = 1", [
  ':nids[]' => $nids
])->fetchCol();
3
  • db_query is deprecated and will be removed in Drupal 9. It shouldn’t be recommended as a solution at this point. You also shouldn’t use the database directly for querying data related to entities; there are APIs for that – Clive Nov 9 '19 at 7:03
  • The recommendation is strictly Drupal 8 update. The current accepted answer no longer works for Drupal 8, because it lacks square bracket. To reject this answer because it does not work for Drupal 9 another major version is disingenuous. It stops users getting an answer that just works. The sentiment is a clear example of, Perfect is the enemy of good enough. – Chris Calip Nov 12 '19 at 23:27
  • The question was asked for Drupal 7; the accepted answer works for Drupal 7. Other answers answered for other versions, but the original question asked for Drupal 7. What @Clive is saying is that, if this answer is showing which code should be used now for Drupal 8, it should avoid using db_query(), since it's deprecated and code should not directly query the database for entity tables. – apaderno May 15 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.