db_query using IN and NOT IN conditions

Public

If you're using db_query to select data from the database, sometimes you need to use the conditions IN and NOT IN to remove some records of you result. But there's a trick to accomplish it.

When you use this kind of condition, you need parenthesis around the placeholders AND the placeholder need to be an array. Check to code.

Get raw version
php
  1. // A db_query statement for Drupal 7.
  2. $results = db_query('
  3. SELECT tid, name
  4. FROM {taxonomy_term_data}
  5. WHERE vid = :vid
  6. AND tid NOT IN (:tid)',
  7. array(
  8. ':vid' => $my_vid,
  9. ':tid' => $my_tid_array,
  10. ))->fetchAll();

This is the same statement for Drupal 8.

Get raw version
php
  1. // The same statement for Drupal 8.
  2. $results = db_query('
  3. SELECT tid, name
  4. FROM {taxonomy_term_data}
  5. WHERE vid = :vid
  6. AND tid NOT IN (:tid[])',
  7. array(
  8. ':vid' => $my_vid,
  9. ':tid[]' => $my_tid_array,
  10. ))->fetchAll();