Pagination Tutorial

Made by: Nick

Pagination is a technique that allows you to show your query's results on multiple pages, just like google (or any other search engine) does with their results. For a nice example of what I mean just visit our homepage and take a look at the bottom of the "Latest News" section.

Lets get started

To make results of a query display on multiple pages there are a few things we will need to know:

  1. how many rows you have in the database
  2. how many rows per page you want to show

This is so that you can work out how many pages you will need and display the links to those pages accordingly. For example if I had a database with 39 rows and wanted to display 10 results on each page then I would have 4 pages (rounded up).

I have created this script for a database full of articles which I will use as an example. If you really want to know then this table in the database had the following fields:

  • id
  • title
  • date
  • article

It is pretty much the same as the paging system I used on the "Latest News" section of this site.

// open database

// set how many rows you want to display per page
$rowsPerPage = 5;

// by default we show first page
$pageNum = 1;

// if page is defined in the URL, use it as page number
    $pageNum = $_GET['page'];

// counting the offset - the first row to be displayed
$offset = ($pageNum - 1) * $rowsPerPage;

// the actual query
$query = " SELECT val FROM articles ORDER BY date DESC" .
         " LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// display the data

// more code to come later

Paging is implemented in MySQL using a multi argument LIMIT query as I have shown above. The first argument specifies the first row to return (the offset), the second specifies the maximum number of rows to return. The "offset" of the first row is 0 (not 1), it is the same in most programming languages.

After displaying the data you want to display for the page you will want to display the links to the other pages for navigation and this is where the other item I mentioned at the start plays a part (how many rows you have in the database), this is used to find out how many pages you will have when using paging, to do this you simply add this between your PHP tags where I have said "more code to come later" (although I will give you the full source code at the end of the tutorial):

// how many rows are there in our database?
$query   = "SELECT COUNT(id) AS numrows FROM articles";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages do we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

After doing this there is nothing left but to display all the navigation links. This code will display the links as follows (where needed):

First Previous 1 2 3 Next Last

Do note that it will display as many links as it needs, so if you have a high number of pages then this might not be the best solution for you (although a quick fix can change it to only display a few page links, I won't show you that in this tutorial).

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
if ($page == $pageNum)                      
$nav .= " $page "; // no need to create a link to current page
$nav .= " <a href=\"$self?page=$page\">$page</a> ";

// create previous and next link and the link to go straight to the first and last page

if ($pageNum > 1)
$page  = $pageNum - 1;
$prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
$prev  = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link

if ($pageNum < $maxPage)
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link

// print the navigation links
echo '<p>' . $first . $prev . $nav . $next . $last . '</p>';

and there we have it, your very own pagination system.

For those who want the whole code to copy and paste then download this file. It has all the source code I have in this tutorial.

Enjoy creating pages,

Feed the Dragon MySpace Tutorials