Show a list of (x) most recent nodes from any of several categories (terms)
Last modified: July 31, 2009 - 04:45
Often one wants to create a list of the most recent posts of any of several categories. What's nice about this php snippet is that it avoids duplicate listings of nodes that are in multiple categories, and will not list unpublished nodes.
Drupal 6.x
<?php
// set the node type
$node_type = 'page';
// set the vocab ID
$vocab_id = 1;
// limit results
$limit = 15;
// use db_rewrite_sql for api compliance
$sql = db_rewrite_sql("
SELECT n.nid, n.title, n.sticky, n.created FROM {node} n
INNER JOIN {term_node} t ON n.nid = t.nid
INNER JOIN {term_data} td ON t.tid = td.tid
WHERE td.vid = %d
AND n.type = '%s'
AND n.status = 1
ORDER BY n.created DESC
");
$sql = db_query_range($sql, $vocab_id, $node_type,0, $limit);
while ($result = db_fetch_object($sql)) {
$items[] = l($result->title, 'node/'.$result->nid);
}
// use proper theme function
print theme('item_list', $items);
?>The example above will only retrieve nodes from a single vocabulary. The snippet below should provide the same functionality for D6 as those below.
Drupal 6.x
<?php
// set the term id
$taxo_id_arr = array(43,511,512);
$taxo_id = join($taxo_id_arr, ',');
// set the number of nodes returned
$list_no = 5;
$sql = db_rewrite_sql("
SELECT DISTINCT n.nid, n.title, n.created
FROM {node} n
INNER JOIN {term_node} tn ON n.nid = tn.nid
WHERE tn.tid IN ($taxo_id) AND n.status = 1
ORDER BY n.created DESC
LIMIT $list_no
");
$sql = db_query($sql);
$items = array();
while ($result = db_fetch_object($sql)) {
$term_names = array();
# gather, into $term_names, all the terms because of which this node was selected:
foreach (taxonomy_node_get_terms($result->nid) as $term) {
if (in_array($term->tid, $taxo_id_arr))
$term_names[] = $term->name;
}
$items[]= l($result->title, 'node/'.$result->nid);
}
if(count($items)) {
print theme('item_list',$items);
}
?>Drupal 5.x
<?php
$taxo_id_arr = array(2,3,4,5);
$taxo_id = join($taxo_id_arr, ',');
$list_no = 5;
$query = "SELECT DISTINCT n.nid, n.title, n.created
FROM {node} n
INNER JOIN {term_node} tn ON n.nid = tn.nid
WHERE tn.tid IN ($taxo_id) AND n.status = 1
ORDER BY n.created DESC
LIMIT $list_no";
$sql = db_rewrite_sql($query);
$result = db_query($sql);
$items = array();
while ($anode = db_fetch_object($result)) {
$term_names = array();
# gather, into $term_names, all the terms because of which this node was selected:
foreach (taxonomy_node_get_terms($anode->nid) as $term) {
if (in_array($term->tid, $taxo_id_arr))
$term_names[] = $term->name;
}
$items[]= l($anode->title, "node/$anode->nid") .
'<br /> (Category/ies: ' . join($term_names, ', ') .
' - date added ' . format_date($anode->created, 'custom', 'd-m-Y') . ')';
}
if(count($items)) {
print theme('item_list',$items) . '<a href=link>more</a>';
}
?>Drupal 4.6 and 4.7
<?php
/**
* Creates a list of node titles selected from multiple category terms
* in descending chronological order (most recent first).
* Titles link to full node.
*
* To change category terms to select from,
* edit the $taxd_id numbers, retaining the "" marks.
*
* To change the number of node titles, edit the $list_no number.
*
* This snippet is tested with Drupal 4.6.x
*
*/
$taxo_id = "3,4,14,23";
$list_no =6;
$query = "SELECT DISTINCT(n.nid), n.title, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid in ($taxo_id) AND n.status = 1 ORDER BY n.created DESC LIMIT $list_no";
$sql = db_rewrite_sql($query);
$result = db_query($sql);
$items = array();
while ($anode = db_fetch_object($result)) {
$items[]= l($anode->title, "node/$anode->nid");
}
if(count($items)) {
return theme('item_list',$items);
}
?>Add the term name of the node, based only in the terms selected (e.g. $taxo_id = "3,4,14,23") and the date.
<?php
$taxo_id_arr = array(3,4,14,23);
$taxo_id = join($taxo_id_arr, ',');
$list_no =6;
$query = "SELECT DISTINCT(n.nid), n.title, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid in ($taxo_id) AND n.status = 1 ORDER BY n.created DESC LIMIT $list_no";
$sql = db_rewrite_sql($query);
$result = db_query($sql);
$items = array();
while ($anode = db_fetch_object($result)) {
$term_names = array();
# gather, into $term_names, all the terms because of which this node was selected:
foreach (taxonomy_node_get_terms($anode->nid) as $term) {
if (in_array($term->tid, $taxo_id_arr))
$term_names[] = $term->name;
}
$items[]= l($anode->title, "node/$anode->nid") .
'<br /> (Category/ies: ' . join($term_names, ', ') .
' - date added ' . format_date($anode->created, 'custom', 'd-m-Y') . ')';
}
if(count($items)) {
return theme('item_list',$items);
}
?>