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.

</> CopyGet 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.

</> CopyGet 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();