[thesite] Tip Harvest: Prev / Next Tip
rudy
r937 at interlog.com
Thu Sep 27 11:44:51 CDT 2001
> 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
More information about the thesite
mailing list