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

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

--
Stand before it and there is no beginning.
Follow it and there is no end.
Stay with the ancient Tao,
Move with the present.

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