Pagination is a helpful concept which is used to display large amount of records. There are two main reasons to paginate results:
It is also useful to sort records according some logical criteria (time/date/surname/price) or let the user select a column to sort by.
I want to show you a simple pagination. We will just display page numbers under the persons list for start. Afterwards, we will add also button for jumping to previous/next and first/last page.
First of all you have to understand how is the pagination implemented in database. In PostgreSQL you have to
OFFSET clauses in the SQL query
to fetch requested records.
This means that SQL query returns only certain amount of results from certain position. To display
pagination controls you need to calculate how many pagination buttons to show, this value can be calculated
only with knowledge of total record count. This ultimately means that you have to run another SQL query
COUNT(*) function to fetch total count of relevant records.
In MySQL you can use
CALC_FOUND_ROWS modifier right after
SELECT clause. And then you can fetch
amount of total rows which would be returned without
LIMIT clause by subsequent
AS all_row_count SQL query. It still means that you have to run two queries, but it is less complicated.
When you know total amount of all possible results, you can calculate amount of pages according to chosen page count. Let’s say that you want certain amount of records per page, then you have to divide total amount of records by page count and round the result up, using ceil() function.
Take a look at an example – you have a table with 105 rows (0–104) and you want 25 items per page:
SELECT * FROM table WHERE col = val LIMIT 25 OFFSET 0; SELECT COUNT(*) AS cnt FROM table WHERE col = val;
Calculations for pagination:
105 records / 25 per page = 4.2 -> ceil(4.2) -> 5 pages
|page 1||page 2||page 3||page 4||page 5|
Be careful to apply same
GROUP BY conditions to
COUNT(*) query as to the
OFFSET clauses. Without them, you wold be displaying page numbers of
The amount of results per page (
LIMIT) is usually a concrete number which a user often cannot change
at all (but it is possible to have such function). Much more important for pagination functionality is
to transmit the information about where to start (
OFFSET) – you have to tell PHP backend which page
you want to render as a response for your request. In your HTTP request, you can send either page number,
which is multiplied by amount of items per page before applying as
OFFSET, or a value which is directly
Use page number (starting from 0) as request parameter. Make current page button inactive or make it distinct visually in another way (so the user can tell which page is he currently browsing).
Keep in mind that when a visitor clicks on a page number, the browser reloads whole page. If the set of listed database records depends on another parameter (i.e. search or sorting), you have to pass also this additional parameter to keep consistent output.
You can use Bootstrap’s pagination classes to make your pagination buttons look good.
Link for first page is easy – just set page parameter to zero. Previous and next page can be calculated by adding or subtracting one from current page value. Last page number can be calculated by subtracting one from page count (because it starts from zero).
Links for previous or first page should be visible only if current page is larger than zero. Similarly, links for next and last page should be visible on other pages than the last one.
If you have search function in your person list, you have to decide if you want to paginate filtered results too. Because there is usually not much displayed records after applying search filter, you would probably want to hide pagination controls in that case:
You can also check the page parameter value for negative values. You can do this by use of mathematical
max(0, intval($_GET['page'])) to avoid
if() statement. Function
max() returns obviously its
larger argument. A combination of
max() can also be handy sometimes –
min(max(0, $v), 100).
Pagination of results is necessary in some cases. When you design your application’s interface, you should think where the amount of displayed records will rise rapidly and implement pagination in such modules. Another approach is to display some “distinguished” results only and let the user search in the rest.
A good idea is to write some general function which will generate HTML structure of pagination for you. Also retrieval of pagination variables for template can be generalized and put into a function. This approach can be extended to hide distant page numbers (in case that there are too many pages).
You will start using frameworks for serious web application development, those have usually pagination support built in.