[RESOLVED] links to previous and next record

[sridharao]

A very common issue, but I couldn't find solution. Please help!

I have a table with an auto_increment id. Since records get deleted, the id number is not continuous or even sorted. I need to display contents of each record one at a time in a page with links to next and previous record at the bottom.

How do I do this?

[Nightwalker83]

How are you displaying the records on an html page or flash page? The reason I ask is when viewing the contents of a database in phpdev one record = one row in the database. All the available record will be visible.

[penagate]

I have a table with an auto_increment id. Since records get deleted, the id number is not continuous or even sorted. I need to display contents of each record one at a time in a page with links to next and previous record at the bottom.

How do I do this?

Off the top of my head:
There are two ways to do this.

In the first case you have already selected a cross-section of records (perhaps even cached them into a view. Well done!) and thus you know all of the IDs when you display the list of records. When you enter a single record, pass the previous and next IDs as parameters.

This is the most efficient method. However...

In the second case (which will inevitably occur regardless of whether you code for the first case) you are displaying a single record and have no idea what the previous and next records are. In this case you need to execute two SELECT queries to find them. You also need to decide on a sort column and order. If the records are naturally ordered by date, you can get "Newer" and "Older" IDs this way (where ? is the date of the current record):

# Newer
select id from records where date>? order by date asc limit 1

# Older
select id from records where date<? order by date desc limit 1

(There may be some way of doing it in one query, but I can't think of it right now.)

How are you displaying the records on an html page or flash page? The reason I ask is when viewing the contents of a database in phpdev one record = one row in the database. All the available record will be visible.

I have no idea what bearing nor relevance this has to the question asked.

[Nightwalker83]

I have no idea what bearing nor relevance this has to the question asked.

I was thinking it being called from the actual html/flash page!

[sridharao]

I solved the issue by using the next and previous id's in the links.