D6: need fix for a php block snippet: display X most recent items in vocabulary

By zilla on 14 Mar 2008 at 05:22 UTC

hello. i was looking at this code from a drupal 5 php block snippet and am trying to get it to work - actually not returning errors, just not showing anything!!!

any idea what's wrong with it? original link is at: http://drupal.org/node/191636

$vocab_id='1';
$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 JOIN term_data td on tn.tid=td.tid WHERE  td.vid=$vocab_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) {
         $term_names[] = $term->name;
  }
  $items[]= l($term->name, "taxonomy/term/$term->tid");
}

if(count($items)) {
  print theme('item_list',$items) . '<a href=link>more</a>';
}

Comments

sorry, other thing is the link for "more" - should go to vocab

zilla commented 14 March 2008 at 05:24

also noticed that 'more link' redirects to admin (when admin is logged in) because it's not seeing anything real to go to?

i'd like that 'more' to go to a vocabulary OR a specified URL (a custom php page for the vocabulary) - can i just replace href=link with an actual path like "href=node/14")?

Why not just use views for

amariotti commented 14 March 2008 at 07:15

Why not just use views for something like this? It would sure make it a lot easier. Your more link could just be replaced with the node page displaying all of the vocabulary items. I'm no PHP coder, but even if I was I still think that I would go with views on this.

A couple of things have

pobster commented 14 March 2008 at 08:24

A couple of things have changed in 6.x, try this (untested);

$vocab_id = '1';
$list_no = 5;

$query = "SELECT DISTINCT n.nid, n.vid, n.title, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid JOIN term_data td on tn.tid=td.tid WHERE  td.vid = $vocab_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) as $term) {
         $term_names[] = $term->name;
  }
  $items[]= l($term->name, "taxonomy/term/$term->tid");
}

if(count($items)) {
  print theme('item_list',$items) . l('more', 'link');
}

Reasons being;

1. Nodes are built using revisions/ versions, so the db query needs to access vid as well as nid.
2. taxonomy_node_get_terms() now takes a node object rather than a nid.

Pobster

interesting...grabbing terms but not titles

zilla commented 14 March 2008 at 13:45

strange - it sort of works in that there *is* output -but it's a list of the terms within the vocabulary, not the most recent titles of nodes across all terms within the vocabulary...any idea why it would call terms and not the node titles?

no views for drupal 6...

zilla commented 14 March 2008 at 13:47

there is no stable views for drupal 6.1, and so i'm reverting to the old style of using php page and block snippets to present content summaries, etc...

It's doing exactly what it's

pobster commented 14 March 2008 at 14:04

It's doing exactly what it's supposed to do... $items[]= l($term->name, "taxonomy/term/$term->tid"); returns the 'term name' with a link to its term ID page. I think perhaps you want a different snippet...?

Pobster

sorry - mean trying to emulate this page snippet!!

zilla commented 14 March 2008 at 21:42

so i'm using this page snippet - and it works great for doing this on a page BUT also including teasers - just trying to find a way to use the same thing in a block such that it shows only titles of the nodes (like most blocks) and not the full teasers...) - this shows teasers for a vocab, most recent first...

  $vid = 5;         // <---- put correct vocabulary ID here
  $terms = taxonomy_get_tree($vid);
  $tids = array();

  foreach ($terms as $term) {
    $tids[] = $term->tid;
  }
  // Note: the number of nodes selected is controlled by 'feed_default_items' from the RSS publishing settings page.

  $result = taxonomy_select_nodes($tids, 'or', 0, FALSE, 'n.sticky DESC, n.created DESC');
  echo "\n<ul>";
  while ($node = db_fetch_object($result)) {
    echo "\n<li>". node_view(node_load($node->nid), TRUE, FALSE, FALSE) .'</li>';
  }
  echo '</ul>';

This snippet is quite a bit

Nick Lewis commented 21 March 2008 at 18:57

This snippet is quite a bit more efficient:

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

--
"I'm not concerned about all hell breaking loose, but that a PART of hell will break loose... it'll be much harder to detect." - George Carlin
--
Personal: http://www.nicklewis.org
Work: http://www.onnetworks.com

you're awesome! any way to truncate characters?

zilla commented 21 March 2008 at 19:24

this works perfectly! thank you!!!

any way to limit number of characters displayed to X (like 72 or whatever) - some titles are wicked long, would love to cap at circa 80 or so...

btw, if i want to add an HTML link to a php block do i need to unset or something? eg. i want to create a custom "read more" link at bottom of block that links to a php page snippet page...

Since this snippet is

Nick Lewis commented 24 March 2008 at 15:45

Since this snippet is getting big, lets turn it into a function. Really building a module, and using hook_block is the easy and right way to do this. Using functions is a good start. Here's an updated version that resolves the duplicate nodes mentioned below, shortens titles, and adds a more link

function node_title_list_by_vocab($vid, $node_type, $title_length = 15, $limit = 15) {
  // use db_rewrite_sql for api compliance
  // DISTINCT(n.nid) to prevent duplicates
  $sql = db_rewrite_sql("
    SELECT DISTINCT(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, $vid, $node_type,0, $limit);
  while ($result = db_fetch_object($sql)) {
    if (drupal_strlen($result->title) > $title_length) {
      // subtract 3 from max to account for the three periods
      $result->title = drupal_substr($result->title, 0, ($title_length - 3)) .'...';
    }
    $items[] = l($result->title, 'node/'.$result->nid);  
  }
  return theme('item_list', $items);
}

// params vid = 1, node_type = 'page', title_length_char_limit 15, list items limit = 15
$output = node_title_list_by_vocab(1, 'page', 15, 15);
// api more link function
$output .=  theme('more_link', 'someurl/banana', 'More...');
print $output;

beautiful

zilla commented 24 March 2008 at 16:59

thanks to all of you - this is a killer php block snippet - it's become very, very functional...

what would it take to perhaps turn this into a module? i think a LOT of beginners and ant-php types would love a block generator for taxonomy/vocan etc for custom content types (versus just default drupal ones for comments and forums) - most people don't seem to understand how to adapt this stuff (myself included!)

nice, but 2 doubts

phano commented 23 March 2008 at 20:58

just a doubt for the perf,
due to the limit, must we put the limit in "db_query_range" or directly in the query (I don't know PHP).

and be carefull, because you could have duplicates if your content is related to several vocabulary in the same category (that's why they introduced the array in the relative article in the handbook: http://drupal.org/node/58861)
the simplest way is to add a "DISTINCT" in the query.

    SELECT DISTINCT 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

You're correct about the

Nick Lewis commented 24 March 2008 at 15:47

You're correct about the distinct thing.
Not sure what you're talking about with the query limit. All I know is that we want to limit results, and db_query_range is the correct function use in that context (unless you want a pager too).
http://api.drupal.org/api/function/db_query_range/6
--
"I'm not concerned about all hell breaking loose, but that a PART of hell will break loose... it'll be much harder to detect." - George Carlin
--
Personal: http://www.nicklewis.org
Work: http://www.onnetworks.com

query limit

phano commented 24 March 2008 at 16:39

it was related to the query limit.
ok if you say it. :-)
all is depending if the query is done (2000 records) and after the function "db_query_range" takes only the 15 first records.
or if the function takes the string, add "LIMIT 15" at the end, and execute the query.
but I don't know.

where to add (what to replace) php snippet for vocabulary

eva commented 31 March 2008 at 04:38

nick,

where would i want to put this snippet exactly? what do i want to replace?

tia,
eva

cut and paste into a block

zilla commented 31 March 2008 at 15:31

eva,
you would first go to admin/modules - enable 'php filter' then go to admin/blocks - add new block, give it a name, cut and paste this code snippet above into the block and be sure that you select 'php' as the input type - it will then show when you move it around into a column in the admin/blocks interface (you'll need to change the vocab id to the right one for your site)

........................................................................
i love to waste time: http://twitter.com/passingnotes

How about the "n"th thru "m"th items?

rkdesantos commented 25 June 2008 at 16:19

Great stuff folks. Thanks.

I'd like to display not the the n most recent items but say the m items after that. For example, my home page displays the most recent 12 nodes in teaser form. Now, I want the block to show links to the titles of the next 12 items after that.

How do I modify the php to get that? (Using D5.7)

Something with: select *

phano commented 11 July 2008 at 12:12

Something with: select * from thetable limit 12 offset 12
must be used in your query.

I don't know if there is an Drupal API to implement that directly, but it's certainly used in the pagination of the homepage where you have too much node to be displayed.

Thanks -- that worked

rkdesantos commented 11 July 2008 at 18:47

Appreciated. That was the SQL command that did the trick.

subscribing...

ambiance7 commented 8 January 2009 at 21:27

subscribing...

D6: need fix for a php block snippet: display X most recent items in vocabulary

Comments

sorry, other thing is the link for "more" - should go to vocab

Why not just use views for

A couple of things have

interesting...grabbing terms but not titles

no views for drupal 6...

It's doing exactly what it's

sorry - mean trying to emulate this page snippet!!

This snippet is quite a bit

you're awesome! any way to truncate characters?

Since this snippet is

beautiful

nice, but 2 doubts

You're correct about the

query limit

where to add (what to replace) php snippet for vocabulary

cut and paste into a block

How about the "n"th thru "m"th items?

Something with: select *

Thanks -- that worked

subscribing...

New forum topics

News items

Our community

Documentation

Drupal code base

Governance of community