howforge.com

I found a good question regarding PHP snippet for Drupal. Usually, we will list nodes in multiple taxonomies using OR operator. However, you might need to list using AND operator which nodes must be in all specified taxonomies instead. It is not easy to do so using plain SQL. The straightforward solution is to use subquery.

$str_tids = array(5,20);
$order = "n.created DESC";
 
$cond = array();
foreach($str_tids as $tid) {
  $cond[] = 'n.nid IN (SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid = '.$tid.')';
}
 
$sql = 'SELECT DISTINCT(n.nid), n.sticky, n.title, n.created, u.uid, u.name FROM {node} n INNER JOIN {users} u ON n.uid = u.uid WHERE '.implode(' AND ',$cond).' AND n.status = 1';
$result = db_query_range(db_rewrite_sql($sql .' ORDER BY '.$order), 10, 10);
if (db_num_rows($result)) {
  $header = array('Title', 'Author');
  $rows = array();
  while ($node = db_fetch_object($result)) {
    $rows[] = array(
      array('data' => l($node->title, 'node/'. $node->nid), 'class' => 'title'),
      array('data' => l($node->name, 'user/'. $node->uid), 'class' => 'author'));
  }
 
  echo theme('table', $header, $rows);
}
?>

Tags: drupal, taxonomy, codenone