By geologyrocks on

Bit of SQL help if possible :)

I need a list of all tags used by a user (whose uid is known) of type X (e.g. blog). I had this to start with:

$result = db_query("SELECT nid FROM {node} WHERE uid=$uid and type='blog'"); 
  while ($node_blog = db_fetch_object($result)) {  
    $terms_result = db_query_range("SELECT count(*) as count, n.tid,t.name FROM {term_node} n INNER JOIN {term_data} t USING (tid) where n.nid =$node_blog->nid");
    while ($term = db_fetch_object($terms_result)) {
      $terms[$i]['name'] = $term->name;
      $terms[$i]['tid'] = $term->tid;
      $i++;
    }
  }

The reason I want it is for a tagadelic block and the kind of query used here is:

  $result = db_query_range('SELECT COUNT(*) AS count, d.tid, d.name, d.vid FROM {term_data} d INNER JOIN {term_node} n ON d.tid = n.tid WHERE d.vid IN ('. substr(str_repeat('%d,', count($vids)), 0, -1) .') GROUP BY d.tid, d.name, d.vid ORDER BY count DESC', $vids, 0, $size);

i.e. I need a count of results too - hence an all in oner query. Anyone help?

⋅ Categories: Drupal 5.x

Comments

deplifer’s picture

deplifer commented

  1. All the tids, tid AND name.

    $rs = db_query('SELECT DISTINCT tn.tid, td.name FROM {term_node} tn INNER JOIN {node} n ON n.nid = tn.nid INNER JOIN {term_data} td ON td.tid = tn.tid WHERE n.uid = %d', $uid); 
    
while($term = db_fetch_array($rs)){
 $terms[] = $term;
}

  2. Distinct tids used by the user, if you only want the tid's.

    $params = db_fetch_array(db_query('SELECT DISTINCT GROUP_CONCAT(tn.tid) as tids FROM {term_node} tn INNER JOIN {node} n USING(nid) WHERE n.uid = %d', $uid));   
    
$tids = explode(', ',$params['tids']);

  3. Count distinc tids used by the user.

    $count = db_result(db_query('SELECT COUNT(DISTINCT tn.tid) FROM {term_node} tn INNER JOIN {node} n USING(nid) WHERE n.uid = %d', $uid));

    But you could just use count on the array of tids above.

geologyrocks’s picture

geologyrocks commented

Got it:

  $result = db_query("SELECT COUNT( * ) AS count, t.tid, t.name, t.vid FROM term_node d, term_data t, node n WHERE t.tid = d.tid AND n.nid = d.nid AND n.uid = %d AND t.vid = %d GROUP BY t.tid, t.name, t.vid ORDER BY count DESC",$uid,$blog_term_id);
  $tags = tagadelic_build_weighted_tags($result, $steps);
  $tags = tagadelic_sort_tags($tags);
  $output = theme('tagadelic_weighted', $tags);
  print $output;

Will print a tag cloud based on a given uid and a vid. Thanks for your help though :)
-----------------------------------------------
GeologyRocks: http://www.geologyrocks.co.uk