Database Discussion - MySQL recordset paging logic

MySQL recordset paging logic

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16175
Printed Date: 29 March 2026 at 4:41am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: MySQL recordset paging logic

Posted By: theSCIENTIST
Subject: MySQL recordset paging logic
Date Posted: 11 August 2005 at 7:36am

I have developed a routine in ASP to page through a recordset in a MySQL database using MyODBC driver, it works fine, currently like this:

[First|Prev] 1 2 3 4 5 6 [Next|Last]

However if I set to show 1 (one) record per page and if I have 100's of records, the numeric links just add up to the total number of records, making the paging area a massive set of numeric links.

What I want is for it to calculate a few links around the current page then in btw add something, like this:

[First|Prev] 1 2 3 ... 97 98 99 [Next|Last]

Does anyone knows the logic behind this? Looked everywhere, nothing, lots of info on it for ADO and paging for Access/MSSQL but MySQL driver does not support it.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Replies:

Posted By: Meson
Date Posted: 23 August 2005 at 6:07pm

MySQL paging command is LIMIT.

LIMIT starting_record, page_size

That's it. You have to use your application language (this case ASP) to do anything else. ASP has to set both the starting record and the page size. That means your page list links must post the appropriate numbers back to the application, so that the query can be resubmitted with the updated LIMIT.

The logic:

retrieve and set the page size and starting record of the page.

update the sql statement's LIMIT clause with the new info.

Requery MySQL

Read out the current page.

Build the previous and next links with the appropriate new starting record and page size as querystrings (or post data if using a form)

Repeat

Posted By: theSCIENTIST
Date Posted: 24 August 2005 at 3:55am

Hi, I have a working routine that does just that.
As it stands it does this:

[Prev] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 [Next]

Viewing page 12 -----------------------^^

It has 25 pages, each page shows 10 records, now I don't want all those links showing, my original question was, how to make it so it will show something like this:

[Prev] 1 2 3 ... 10 11 12 13 14 ... 23 24 25 [Next]

Viewing page 12 -----------^^

Note that its nice to contain some initial pages, then also some around the currently viewed page, and at last a few of the last pages, the middle area should change dynamically as we navigate.

What's the code logic behind this? Pseudo or real code examples welcome.

I can post my working code, at this point I just want to understand the working logic so I can test around it.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: Meson
Date Posted: 13 September 2005 at 6:54am

You have to implement it in asp. I have no idea how to do it.

Posted By: theSCIENTIST
Date Posted: 13 September 2005 at 11:55am

Well, yes, in ASP, but the logic would probably be the same for other languanges.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com