Re: Return column value from previous and next row in a recordset

On Sun, 12 Feb 2006 17:09:04 +0800, Matt Arnilo S. Baluyos (Mailing Lists)
wrote:

> Hello everyone,
>
> I'm trying to implement a sort of Master-Detail functionality in a
> website. I have a page that displays a list of articles. Clicking on an
> item in that page will show more detailed information on that article.
>
> What I'd like to be able to do is to be able to put in "Previous" and
> "Next" links on that detailed page - and I'd like to do that without an
> additional query to the database just to determine the previous and next
> article's IDs.
>
> I was thinking if there'd be a way to do return a recordset such as this
> one:
>
> article_id | article_title | previous_article_id | next_article_id 1
> article1 NULL 2 2
> article2 1 3 3
> article3 2 4 4
> article4 3 NULL
>
> Any pointers would be greatly appreciated.
>
> Thanks and best regards,
> Matt

Matt,

I may not completely understand how your news archive is going to work.
But, if the articles are always going to be store in sequential format,
there shouldn't be a need for additional columns to track previous and
next. Nevertheless, even if you're adding the additional columns (I
presume so you can control the order of articles), then why not simply
query the article_id, previous_id and next_id into an array and work with
these values from the array. You'd only be hitting the database once
unless you're talking about thousands of articles, in which case I would
recommend creating some type of paging algorithm to break the result sets
into more "memory friendly" chunks.

-Ken