On 7/24/07, <b class="gmail_sendername">Earl Miles</b> <<a href="mailto:merlin@logrus.com">merlin@logrus.com</a>> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
This can be much more efficiently done in two queries where you do a limit 1<br>and do > on one and < on the other, using the sort criteria, to determine what<br>next/previous is.<br><br>For example, if you're sorting on created, you do a SELECT nid FROM node WHERE
<br>[sufficient clauses to restrict to just this forum] WHERE node.created >=<br>$node->created AND node.nid <> $node->nid LIMIT 1<br><br>And reverse it to find the previous.<br></blockquote></div><br clear="all">
I did something similar for a client. They wanted to display the thumbnails of<br>the next/previous image below each image node.<br><br>What I did is go by the node ID.<br><br>So this function does what you mentioned above.
<br><br>function pn_node($node, $mode) {<br> switch($mode) {<br> case 'p':<br> $op = '<';<br> $order = 'DESC';<br> break;<br><br> case 'n':<br> $op = '>';
<br> $order = 'ASC';<br> break;<br><br> default:<br> return NULL;<br> }<br><br> $sql = "SELECT n.nid FROM {node} n<br> WHERE n.nid $op %d<br> AND n.type in ('image', 'video')
<br> AND n.status = 1<br> AND n.promote = 1<br> ORDER BY n.nid $order<br> LIMIT 1";<br><br> $n_nid = db_result(db_query($sql, $node->nid));<br> if ($n_nid) {<br> $n_node = node_load($n_nid);<br> if ($n_node->type == 'image') {
<br> return l(image_display($n_node, 'thumbnail'), "node/$n_nid", array(), NULL, NULL, FALSE, TRUE);<br> }<br> }<br>}<br><br>Then in the node-image.tpl.php file, I have this:<br><br><?php<br>
if ($page) {<br>
print pn_node($node, 'n');<br>
print pn_node($node, 'p');<br>
}<br>
?><br>
<br>It is not a bottleneck as far as queries go, but it does get executed a lot.<br><br>The issue with forums is that you have to join the term_node too and filter by<br>the forum, which will add to the query time.<br>--
<br><a href="http://2bits.com">2bits.com</a><br><a href="http://2bits.com">http://2bits.com</a><br>Drupal development, customization and consulting.