The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 4 of 4

Thread: Pagination vs ID sequence gaps

  1. #1

    Pagination vs ID sequence gaps

    I've Googled this issue like crazy and I've come up dry.....

    How on earth on do you get pagination to look at the actual ID's of MySQL entries instead of following PHP's generated numeral ordering?

    I have a CMS where the user can add and delete entries, however when they delete them, this of course, leaves gaps in the ID order. Far as my pagination knowledge goes, my script tells it to +1 after each ID to get to the next entry.

    Any suggestions would be most appreciated.
    Thanks guys

  2. #2

    You could do that, but you could also just...

    Setting ID's in your DB through your script is messy, as there is a good reason MySQL creates their own, this guarantees a unique ID each time.

    My suggestion is this: When a visitor initially goes to the area of the site with these "pages" have your PHP generate a mulitdimensional array that corresponds your "page numbers" (which increment by 1) with the ID's in your DB. Your array should look something like this:

    [1][3]
    [2][8]
    [3][9]
    [4][15]
    [5][17]

    The first column is the "page number" and the second is the ID stored in the database. This way, when a visitor wants the see "page four", your PHP looks in the array, finds that the fourth entry has an actual ID of 15, then queries that entry from your DB. It takes a little more PHP, but it leaves your unique ID's intact and doesn't risk messing them up.

    If you'd still like to affect your DB directly and force ID's to be sequential, with no gaps, you'd have to force the ID's with PHP every time something was added, pulling the responsibility of ensuring unique id's upon yourself. If you want to do this, just leave the column that MySQL updates automatically and create your own secondary ID column, as, I believe, MySQL makes you create an auto-incrementing key for a table.


    Quote Originally Posted by sugar View Post
    I've Googled this issue like crazy and I've come up dry.....

    How on earth on do you get pagination to look at the actual ID's of MySQL entries instead of following PHP's generated numeral ordering?

    I have a CMS where the user can add and delete entries, however when they delete them, this of course, leaves gaps in the ID order. Far as my pagination knowledge goes, my script tells it to +1 after each ID to get to the next entry.

    Any suggestions would be most appreciated.
    Thanks guys

  3. #3
    Generally, PHP pagination uses MySQL's LIMIT. This shouldn't depend on your ID (i.e. you shouldn't be selecting the +1 entry, you should be selecting a range of entries based on the page number and grabbing all of them in one query )
    got pwnt?

  4. #4
    Quote Originally Posted by hl View Post
    Generally, PHP pagination uses MySQL's LIMIT. This shouldn't depend on your ID (i.e. you shouldn't be selecting the +1 entry, you should be selecting a range of entries based on the page number and grabbing all of them in one query )
    word

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012