Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder DR.Wong's Avatar
    Join Date
    Jan 2005
    Posts
    360
    Thanks
    23
    Thanked 1 Time in 1 Post

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

    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!
    -DR.Wong

    Wheres the food at?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder DR.Wong's Avatar
    Join Date
    Jan 2005
    Posts
    360
    Thanks
    23
    Thanked 1 Time in 1 Post
    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?
    -DR.Wong

    Wheres the food at?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    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.

  • #5
    Regular Coder DR.Wong's Avatar
    Join Date
    Jan 2005
    Posts
    360
    Thanks
    23
    Thanked 1 Time in 1 Post
    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?
    -DR.Wong

    Wheres the food at?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •