How to Make 'Next 5' Link on list page.

[DR.Wong]

Hey everyone.

I have asked this question somewhere here before and was just told to learn more about database normalisation. Yeah.. that didnt really help.

I have looked around a lot and tried a few things but there is something that I cannot figure out (it must be simple though) and btw I hope this is the right catagory to post this in.

What I want to do:

I have a MySQL database full of news entries. The table layout for these entries is as follows:

1) ID // Primary Key // Auto Increment // INT
2) Title // VARCHAR
3) Text // TEXT

The unique ID's may not be completely chronologically true as entries are added and more importantly DELETED on a regular basis, so it may happen that the ID's go : 1,2,5,6,9 and not 1,2,3,4,5.

These ID's also cannot be 'reset' or ordered again as to make the pattern complete as the ID is used to reference comments joined in a seperate table.

All I want to do is show 5 entries per a page and have a 'Next 5' and if relevant 'Previous 5' link on the bottom of my page.

An idead I had was simply to add a nother column to my table as a 'countID' that can be re-ordered at will to allow for additions and subtractions and a complete number pattern, but I do not know how to 'reset' a column.

If you have a solution to my query or can point me in the right direction, please do!!

Thanks a lot!

[guelphdad]

Are you using PHP or ColdFusion or something else to display your pages? The question isn't really mysql related (after all you know how to retrieve your data), so I can move the thread to the appropriate area where you will likely get help quicker.

As for normalization, depending on the structure of your table, yes it would be more important in some cases to correct that before working on code to display your links. The problem could be you have your ids in a list, in which case it would not make it easy to determine the previous and next links for example.

[DR.Wong]

I am indeed using PHP to display my list (sorry for not stating it) yes, please move the thread to the PHP section, thanks a lot.

So I take it I should go ahead and create that new column that will have an ID for each entry in chronological order no matter what?

[guelphdad]

no you should not make a new column. you would be able to find the next and previous 5 links even if there are gaps in your id sequence.


select
idcolumn
foo,
bar,
qux
from yourtable where idcolumn IN
(select idcolumn from yourtable as high
where idcolumn > $idcolumn
order by idcolumn
limit 5
union all
select idcolumn from yourtable as high
where idcolumn < $idcolumn
order by idcolumn desc
limit 5)


will give you the five ids above and below any specific id which you would have set in the variable $idcolumn.

and you can return any columns of course denoated by foo, bar, qux above.

you would use your php code to iterate over your list.

[DR.Wong]

thank you!

I tried a very similar method before, but (from what I understand) this piece of code has the same problem as mine does.(or perhaps I am just a bit blind)

The big issue is getting the variable '$idcolumn', since I do not know what number is first as they are not in order, I cannot find the next variable after the last entry displayed on my page.

Say the last entry on the first set of 5 was 'id=5', I obviously want the first entry of the next set of 5 to be the entry directly after 5, but lets assume in this case, there is no 6, how will the code no to go to 7?

Also, and my great appologies for not stating this earlier, a nother factor that threw a spanor in the works was the fact the the newest entries would have to be shown first, so the highest ID would have to come first and then the 5 lower that it would follow.

Am I making any sense?