Handling pagination

Pagination involves providing a limited subset of the results of a database query. This is usually done for display purposes, but could easily apply to other situations. At first glance, it would seem the LimitIterator class is ideally suited for the purposes of pagination. In cases where the potential result set could be massive; however, LimitIterator is not such an ideal candidate, as you would need to supply the entire result set as an inner iterator, which would most likely exceed memory limitations. The second and third arguments to the LimitIterator class constructor are offset and count. This suggests the pagination solution we will adopt, which is native to SQL: adding LIMIT and OFFSET clauses to a given SQL statement.

How to do it...

1. First, we create a class called Application\Database\Paginate to hold the pagination logic. We add properties to represent values associated with pagination, $sql, $page, and $linesPerPage:

   namespace Application\Database;
   
   class Paginate...