0

I ran into a snag setting placeholders for the IN operator, and then I ran into a webpage that basically says, if I'm reading it correctly, that they are not compatible. I also found a forum post on this subject in which the questioner gave up and expanded his IN into a series of ORs.

Apparently, I have the choice of either expanding the IN into ORs or learning to do dynamic queries. Since db_select() apparently comes with a lot of overhead, I suppose the ORs will run faster.

Is this correct, or is there a way to use IN with db_query?

1

You can use placeholders in IN conditions, but you need to set them up correctly first. Using the example in your first link, this should work:

$replacement_values = array();
foreach(array_values($your_values) as $i => $val) {
  $replacement_values['@val' . $i] = $val;
}
// Should look something like:
// $replacement_values = array(
//   '@val1' => 'your first value',
//   '@val2' => 'your second value',
// );

$placeholders = array_keys($replacement_values);
$placeholders = implode(', ', $placeholders);
// Should look something like:
// $placeholders = '@val1, @val2';

$res = db_query('SELECT field FROM {table} WHERE cond_field in(' . $placeholders . ')', $replacement_values);

n.b. I haven't tested this so it might need some tweaking.

That said, I'd urge you to look into dynamic queries. They're not that hard to get your head around and if you wan't to right modules that might ever be used on another Drupal site that uses an alternative database to MySQL then they are a must.

1

You can easily use placeholders inside db_query() with IN condition. Just pass array as a value for placeholder and don't do implode()!

Here is working example:

$types = array('article', 'poll');
$res = db_query('SELECT * FROM {node} WHERE type IN (:types)', array(':types' => $types));
$nodes = $res->fetchAllAssoc('nid')
1

I got this from the Static Queries page on d.o where they used db_query for their example:

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 don't know what exactly you're doing since you haven't provided any code, but this is essentially what Eugene Fidelin and Paul Querol have said in their answers. (Although Paul Querol's answer is the 'expanded' second part of the code above)

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.