[thesite] Tip Harvest: Prev / Next Tip

[rudy]

hi joshua

>Let's say you've got a many-to-many relationship between items and
>categories with a third table stuck in the middle to create the
>associations.  Categories have a primary key id, and items have a primary
>key id.  But, let's say I want to select the next and previous item from
>within the same category based on a field other than the primary key, such
>as the date it was created.

specifically, prev/next item based on item create date within
the same category

cries out for a working example...


>1.  When I get to the end or beginning of a category, I want to display
> theitem in the adjacent category (determined by some other arbitrary
> sort field having to do with the order of the categories)

i actually tried this once, it's uuuuuuuuugly

considering how the user might be confused by suddenly popping into the
adjacent category by following "next" links within a category, and the
extra user-interface explanation you might have to add to the link in that
case, i would just show "end of category" marker, or simply gray out the
"next" link, and let the user go "up" a level to choose the next category
manually...

>2.  When I get to the end or beginning of a category, I want to return
> "end of category..." in some form or fashion.
>
> Can this be done in a single query?

yeah, same basic approach i outlined to dean last night, subselects
 which should return a null if there isn't a prev/next item


cries out for a working example...

unfortunatley, not enough hours in the day

have to add it to the pile of things to do...

... somewhere within which is my promise to
build a data model for evolt's database, which
i'm feeling really guilty about now...


rudy