By mean0dspt on

I need a quiery, that selects node title, nid and attached image iid of nodes of selected type and only if they are tagged with specific taxonomy term in a list from 3 last nodes (desc.)

the code output of 1 list item should look like this:

<li><a href="link to node" title="node title">node title<img src="thumbnail of the image attached to node" alt="image title" title="image title" class="image image-thumbnail " /></a></li>

I understand that I'll need something like this (modified standard node_title_list function)

function imaged_node_title_list($result, $title = NULL) {
  $items = array();
  $num_rows = FALSE;
  while ($node = db_fetch_object($result)) {
    $items[] = l($node->title. image_display($image,'thumbnail'), 'node/'. $node->nid, !empty($node->comment_count) ? array('attributes' => array('title' => format_plural($node->comment_count, '1 comment', '@count comments'))) : array());
    $num_rows = TRUE;
  }

  return $num_rows ? theme('node_list', $items, $title) : FALSE;
}

but I can't figure out the quiry
the best shot so far was

SELECT node.nid AS nid, 
node.title AS node_title, 
image_attach.iid AS image_attach_iid, 
node.created AS node_created 
FROM node node 
INNER JOIN term_node term_node ON node.vid = term_node.vid 
LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid 
WHERE (node.status <> 0) AND (node.type in ('story')) AND (term_node.tid = 451) 
ORDER BY node_created DESC

but it only manages to build a list with correct links, nothing more
any suggestions? am I going in the right direction?

⋅ Categories: Drupal 6.x

Comments

mean0dspt’s picture

mean0dspt commented

Ok, so there's a dismatch of quiery items and function.
I figured out that $node->node_title instead of $node->title prints out title ok
but I can't figure out the right substitute for $image

when I try to print_r the array to get the idea of the items order, the site crashes

joachim’s picture

joachim commented

Why not use Views for this?

The problem with your code is that $node is an object whose properties are the same as the column names in the query. So whatever you use in your AS clauses is what you'll get in the $node object.

mean0dspt’s picture

mean0dspt commented

I actually tried using Views and it can't offer link(one field+second field), only link+another link
the image_display() function is not documented ith drupalAPI so I'm not sure about what to put there

mean0dspt’s picture

mean0dspt commented

Ok, after almost a day of messing around the code I figured out the right function that does what I want. In case anyone else will need such thing:

function imaged_node_title_list($result, $title = NULL) {
  $items = array();
  $num_rows = FALSE;
  while ($node = db_fetch_object($result)) {
    $items[] = l($node->node_title.image_display(node_load($node->image_attach_iid), IMAGE_THUMBNAIL), 'node/'. $node->nid, array('html' => true));
    $num_rows = TRUE;
  }

  return $num_rows ? theme('node_list', $items, $title) : FALSE;
}
reub77’s picture

reub77 commented

I'm new to Drupal and need this. How do I apply this to my site? template.php? to the page?

mean0dspt’s picture

mean0dspt commented

you put the code above in template.php and then output in a block or elsewhere, stating some variables directly (like term_node.tid or number of elements in the list)

<?php
$output = phptemplate_imaged_node_title_list(db_query_range(db_rewrite_sql("SELECT node.nid AS nid, 
node.title AS node_title, 
image_attach.iid AS image_attach_iid, 
node.created AS node_created 
FROM node node 
INNER JOIN term_node term_node ON node.vid = term_node.vid 
LEFT JOIN image_attach image_attach ON node.nid = image_attach.nid 
WHERE (node.status <> 0) AND (node.type in ('story')) AND (term_node.tid = 451) 
ORDER BY node_created DESC"), 0, 3));
print $output;?>

My PHP skills are mediocre, there might be a better written code, but it works