Re: FN-FORUM: Choosing the next record in MySQL DB

date posted 14th May 2008 10:54

On Wednesday 14 May 2008 12:31:38 Tom Wilson wrote:
> Try this,
>
> (select * from news where ID UNION
> (select * from news where ID >= $id order by ID ASC LIMIT 2)
>
> This returns the rows your after and the surrounding two rows either
> side, so if $id=8 it will return rows 8,7 and 9.

If 8 is the first or last ID then you'll only get two rows and you'll have
lots of work sorting it out.

Don't know why everyone wants to make this so complicated.

Select what you need (not "select *") to display the page for the current ID.

Select ID for next page, select ID for previous.

It's simple, it's maintainable. *If* there's a performance issue, then start
getting clever with stored procedures, complicated queries, what have you:
but in that case I'd be at least considering denormalising and having the next
and previous IDs in the same row to keep the database server load to a
minimum.