[thesite] Tip Harvest: Prev / Next Tip
Dean Mah
dmah at shaw.ca
Thu Sep 27 15:35:08 CDT 2001
rudy writes:
> >Interesting. Still running a query against the database though...
>
> what do you mean "still" running?
>
> when you run the query to get the tip details, i'm suggesting just
> use that query to get the prev/next for display under that tip's details
Yeah. What I meant is that you are still doing extra select
statements to get the data. Just instead of grabbing the entire
ordered list, you are grabbing the next and prev tip_id's.
> but think about how you produced the summary list
>
> you ran a query for that month, right?
>
> [ or else i guess i don't know where
> your data is coming from -- i just assumed
> that everything is in the tips table(s)... ]
Actually, no. I'm getting tips for the past week and joining against
the users table and the tip_types table. Just wait when we allow tips
to be of multiple type!
Here's the query:
SELECT tip_id, tip_date, tip_type_name, author_id, who,
attrib_type, attrib_author
FROM thetips, users, tip_types
WHERE tip_date BETWEEN to_date('#startdate#', 'YYYY-MM-DD') AND
to_date('#enddate#', 'YYYY-MM-DD')
AND author_id = userid (+)
AND thetips.tip_type_id = tip_types.tip_type_id (+)
ORDER BY #orderby# #orderdir#
> and when you click on one of those column headings,
> this attaches a query string to the url, orderby=xxxxx
>
> i'm assuming this simply re-runs the query that produces the
> summary list, just using a different order by clause
Yes. It also sorts ASC or DESC based on which way you sorted on the
field previously. (I needed to add those little up/down arrow icons
to make that more obvious.)
> > tip_detail.cfm?tip_id=3343&tip_list=3,3343,1,4423,410
>
> pass only prev/next? that won't work, cause after the first page,
> how do you know what the next tip id is?
[ re-ordered context ]
> thus, your quest for "passing" the entire summary list
Exactly.
> (b.t.w. what's the 410 for?)
Simply random number used for the example. It would represent another
tip_id in the list. Assume a potentially sparse table where not all
tip_id's refer to an actual tip, e.g., tip was deleted after it was
loaded.
> you could do it if they were stored in some persistent way in cold fusion
>
> application session variable? cached query?
Ummmm... those are beyond me at the moment. I've only ever read
Seth's code and I don't think that he used app. sess. var. or cached
queries.
> we've had this discussion before, it's one of my favourite threads
> [but maybe only because it's so easy for me to forget things at my
> age...]
Don't get me started on that.... Damn, forgot the witty aphorism that
I was going write here.
> my suggestion lets you go from detail page to detail page following
> prev and next links without "passing" any of them
Can you re-write your query based on the above query, i.e., taking the
joins into account?
> you also need to know when you are sitting on the first or the last,
> so that you can "gray out" those links
That's fine. The query comes back with null values or at least ""
that I check to remove the links as appropriate. Unless you're
talking about some kind of cached query here. In which case, I
gotcha.
Dean
More information about the thesite
mailing list