35

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?

43

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)");
?>
  • 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
18

For Drupal 8

Entity query:

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

Sql Query (select), essentially the same for other query types.

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

For Drupal 7

See Berdir's answer.

For Drupal 6

You can do it like this:

$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 which will creating a string that holds the placeholders needed for the array of values given. Drupal 7 handles all this internally like Berdir describes.

9

Using the Database API in Drupal 7

Here's how you can use db_select() instead of db_query() for the same results.

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

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

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.