I'm currently working on a project in which I'm trying to create PHP web-based paging for our tables that have over a million rows. I've figured out that I have three options:
Retrieve all rows in resultset (Not possible due to the size)
Retrieve 1,000 rows, put in temporary table and create an iterator for it and page through it
Run a query each time if someone goes page forward or backward
I'm working with option #3. I have the first page showing up as
"select * from account order by acct fetch first 10 rows only"
"select * from account where acct>(last record) order by acct fetch
first 10 only"
page last record
"select * from account where acct=(select max(acct) from account)"
The problem is that it keeps showing the previous page! Any help would be appreciated.