How to query sqlite for specific rows i.e. page share (perl DBI)

Question

Sorry for my noob question, I am currently writing a perl web application with sqlite database. I would like to be able to show in the results of my query applications that can receive thousands of lines - they need to be divided into pages - the routing should be like / webapp / N - where N is the page number. what is the correct way to query sqlite db using DBI to retrieve only relevant rows.

for example, if I show 25 lines per page, so I want to request db for 1-25 lines on the first page, 26-50 on the second page, etc ...

Thanks, advanced!

Answer 1

Using the LIMIT / OFFSET construct will show pages, but OFFSET makes the query ineffective and makes the contents of the page disabled when data changes.

This is more efficient and consistent if the next page launches the request where the last page ends, for example:

 SELECT * FROM mytable ORDER BY mycolumn WHERE mycolumn > :lastvalue LIMIT 25 

This means that your links are not /webapp?Page=N , but /webapp?StartAfter=LastKey .

This is explained in detail on the cursor scroll page .

Answer 2

You should do something like this:

 SELECT column FROM table ORDER BY somethingelse LIMIT 0, 25 

and when the user clicks on page 2, you should do:

 SELECT column FROM table ORDER BY somethingelse LIMIT 25, 50 

etc.

Answer 3

Most likely you will use the keywords LIMIT and OFFSET, for example:

 $sth->prepare("SELECT foo FROM bar WHERE something LIMIT ? OFFSET ?"); $sth->execute($limit, $offset); while ( my @row = $sth->fetchrow_array ) { # loop contains 25 items 

The $limit and $offset variables will be controlled by the parameters passed to your script using html / cgi / any functions.

Answer 4

Pagination is one of those problems that many CPAN modules have already solved. If you use direct SQL, you can look at something like DBIx :: Pager . You can also check out something like Data :: Pageset to help you create links to various pages. If you use DBIx :: Class (which is a great tool) for your SQL queries, DBIx :: Class :: ResultSet :: Data :: Pageset will make this very easy for you.

Essentially, SQL processing is one way, but you also need to solve various problems in the aspect of the template. I would advise you to take a look at these modules and maybe even push the CPAN a bit to see where someone else has made a hard climb for you regarding pagination.