how to query sqlite for certain rows, i.e. dividing it into pages (perl DBI)
dbi, perl, sqlite
Solution
Using the `LIMIT`/`OFFSET` construction will show pages, but the `OFFSET` makes the query inefficient, and makes the page contents move off when the data changes.
It is more efficient and consistent if the next page starts the query at the position where the last one ended, like this:
SELECT *
FROM mytable
ORDER BY mycolumn
WHERE mycolumn > :lastvalue
LIMIT 25
This implies that your links are not `/webapp?Page=N` but `/webapp?StartAfter=LastKey`.
This is explained in detail on the Scrolling Cursor page.
Problem
sorry for my noob question, I'm currently writing a perl web application with sqlite database behind it. I would like to be able to show in my app query results which might get thousands of rows - these should be split in pages - routing should be like /webapp/N - where N is the page number. what is the correct way to query the sqlite db using DBI, in order to fetch only the relavent rows. for instance, if I show 25 rows per page so I want to query the db for 1-25 rows in the first page, 26-50 in the second page etc....