Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I want to limit the results of my code snippet below to query only items that have been used in a particular content type. How do I do that

<?php

$vid = 15; //vocabulary id
$num_term = 20; //limit maximum terms

//USE INNER JOIN below to only show terms with a count of 1 or more (the LEFT JOIN includes terms with a count of 0)
$query = "SELECT td.tid AS tid, name, COUNT( nid ) AS count
FROM {term_data} AS td
LEFT JOIN {term_node} AS tn ON td.tid = tn.tid
WHERE td.vid=". $vid ."
GROUP BY tn.tid ORDER BY name ASC LIMIT ". $num_term;
$result = db_query($query);
while ($term = db_fetch_object($result)) {
$items[]= l($term->name, "taxonomy/term/$term->tid") . ' (' . $term->count . ')';
}

if(count($items)) {
return theme('item_list',$items);
}

?>
share|improve this question
    
Any special reason as why you're not using views to attain this functionality? –  mariomc Nov 15 '12 at 13:40
add comment

1 Answer

up vote 1 down vote accepted

You would also join in the node table (on term_node.nid = node.nid) and add node.type to the WHERE clause.

Do note there are several problems with your above code. First and foremost, as mariomc asks, any special reason why you're doing this manually instead of through Views?

Second, if you do have a good reason to do the querying yourself, do look into using placeholders with db_query. Its primary purpose is preventing SQL injection problems (strictly speaking, if the variables really are hard coded like this, there is really no danger, but consider what happens when you decide to feed them in from somewhere else at a later date).

All in all, code that would both answer your question and is more "correct" would be (untested):

<?php

$vid = 15; //vocabulary id
$num_term = 20; //limit maximum terms
$type = 'story'; // Your type

$query = "SELECT td.tid AS tid, name, COUNT( nid ) AS count
  FROM {term_data} AS td
  LEFT JOIN {term_node} AS tn ON td.tid = tn.tid
  LEFT JOIN {node} AS n ON tn.nid = n.nid
  WHERE td.vid=%d AND n.type = '%s'
  GROUP BY tn.tid ORDER BY name ASC LIMIT %d";

$result = db_query($query, $vid, $type, $num_term);

// ...
?>
share|improve this answer
    
I have a view code but the view does give me the itemized list but does not produce a page showing the necessary content... (expect that view code here drupal.org/node/343294#comment-5224920) ... THNKS... know anything about exposed filters? can I use this as an exposed filter for another view? –  Sun Rhythms Nov 15 '12 at 14:15
    
@SunRhythms If you have a view, you don't need to write code that queries the database directly with db_query(). –  kiamlaluno Nov 15 '12 at 14:31
    
sorry man... getting an error... [[ ... Error message Recoverable fatal error: Argument 2 passed to db_query() must be an array, integer given, called in /home/miequity/public_html/modules/php/php.module(80) : eval()'d code on line 14 and defined in db_query() (line 2313 of /home/miequity/public_html/includes/database/database.inc) ... ]]... –  Sun Rhythms Nov 15 '12 at 15:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.