[thesite] Tip Harvest: Prev / Next Tip

[rudy]

> So I need the logical prev and next tip_id as generated by the sort.
> Any thoughts?

when you go to retrieve the tip details from the database, 
use subselects to get them 

the subselects look complicated, but they are simple and 
more or less efficient

apologies if the table/column names are wrong, here's an example

it is generalized to accept any sort sequence, but relies on an
ascending primary key sequence, in this case tip_id, to provide 
the subsequence (i.e. what to do if the same author has multiple tips)

   set seqfield="author"

   select tipdetails
    ,  ( select tip_id from tiptable
           where tip_month = #this_month#
             and tip_id =
                 ( select MAX(tip_id) from tiptable
                     where tip_month = #this_month#
                         and ( tip_#seqfield# < OUTER.tip_#seqfield#
                              or tip_#seqfield# = OUTER.tip_#seqfield#
                                          and tip_id < OUTER.tip_id ) 
                 )  ) as prev_id
    ,  ( select tip_id from tiptable
           where tip_month = #this_month#
             and tip_id =
                 ( select MIN(tip_id) from tiptable
                     where tip_month = #this_month#
                         and ( tip_#seqfield# > OUTER.tip_#seqfield#
                              or tip_#seqfield# = OUTER.tip_#seqfield#
                                          and tip_id > OUTER.tip_id ) 
                 )  ) as next_id
      from tiptable OUTER
         where tip_id = #thistip#


>... or I could pass the ordered list in from the tip summary template.  

can you describe how "pass in" would work?

where's the ordered list stored?  a cf array?

this is actually the most efficient way

i just showed you the sql in case you ever find the need to do prev/next 
links in some other application...

;o)


rudenstein