Simple Pagination

What is pagination?

Simply put, pagination is splitting up many results in to bite size chunks (pages) that helps the user quickly sort through the results instead of having to scroll way down the page to find what they’re looking for.

In order to paginate our results, we need to know how many results there are and how many we would like displayed on each page. In this example, we’ll make a simple function to display one row of data for each record in the result set and split the results in to the desired number of pages.

I’ll build the script slowly so you can see what’s going on. First, we’ll place all of our code inside a function called paginate(). This function takes three parameters, $perpage (how many results to display per page), $table (the database table you are pulling results from) and $field (the field of the table that you wish to display). Admittedly, this is quite a basic function but it should give you the general idea behind pagination.

function paginate($perpage,$table,$field) {
}

To display the next/previous page of results, a user will click on a link. Clicking the link will pass a parameter in the url for our script to access. This parameter will be assigned to a variable and used to determine the starting point of the result set (results 0-5, 10-15 etc). If the parameter hasn’t been passed, we’ll set it to ‘0′.

// define from which record to start the query from.
if (isset($_GET['firstresult'])) {
	$firstresult = (int) ($_GET['firstresult']);
	} else {
	$firstresult = 0;
	}

The next thing we need to know is how many results are in the database table. We’ll use this information to decide how many pages are needed. To count the number of rows in the database, we use the aptly named MySQL function COUNT inside a SELECT statement and assign the number to the variable $count.

//count how many rows in the database and assign the result to $count
$countquery = "SELECT COUNT(*) FROM `$table`";
$countarray = mysql_fetch_array(mysql_query($countquery));
$count = $countarray[0];

We can then work out how many pages are needed by dividing the number of rows in the database ($count) by the number of results to display per page ($perpage). The ceil() function then rounds this up to the next whole number. You can’t have 1.66 pages to display!

//how many pages will be needed to display all records
$pages = ceil($count/$perpage);

Next comes the important part. We query the database utilising the LIMIT function. This takes two parameters, the starting point from which to pull rows and how many rows to pull out. For example, ‘LIMIT 0,5′ will pull out the first 5 rows from the database whereas ‘LIMIT 15,5′ will pull out 5 rows starting from row 15. In our query, we’ll use the variables $firstresult and $perpage as defined earlier. Remember, if a link hasn’t been clicked, $firstresult is set to 0.

//pull results from database for this page
$query = "SELECT * FROM `$table` LIMIT $firstresult,$perpage";
$results = mysql_query($query);

Now we have all the necessary information to start building our html output. We start by creating a html list of results.

//produce a html list showing the results
$output = "<ul>\n";
while ($list = mysql_fetch_assoc($results)) {
	$output .= '<li>' . $list[$field] . '</li>';
        }
$output .= "</ul>\n";

For the next part we need to produce the links to the other pages (if necessary). If so, we determine the current page number by dividing $firstresult by $perpage and adding 1. To make this easier to understand, imagine that we are showing 4 results per page and we’re on the second page:

// check to see if 'next' and 'previous' links are needed and create
// numbered links to other pages.
if ($pages > 1) {
	$thispage = ($firstresult/$perpage) + 1;
        //translates as (4/4) + 1 = 2
        //$thispage = 2

Once we have the current page, we can use it as a visual helper for the user and add it to the $output.

$output .= "<p>Page $thispage of $pages</p>";
//translates as 'Page 2 of 4' for example

If the current page number is higher than ‘1′ we need to create the ‘previous’ link (i.e add it to $output). The firstresult parameter is set to the current $firstresult minus $perpage (in other words, the page before).

if ($thispage > 1) {
		$output .= '<a href="pager.php?firstresult=' . ($firstresult - $perpage) . '">Previous page</a>';
 
....
}

Then we build the numbered page links. We loop through the number of pages required and for each one create a link to the appropriate result set.

$output .= '<p>';
	for ($i = 1; $i <= $pages;$i++) {
		$output .= '<a href="pager.php?firstresult=' . ($perpage * ($i - 1)) . '">'.$i.'</a>';
                //translates as <a href="pager.php?firstresult=5 * (2 - 1))">2</a>';
                //firstresult parameter = 5
	}
	$output .= '</p>';

Finally, we check to see if a ‘next’ page is required. This simply adds $perpage to $firstresult and passes it as the firstresult parameter.

if ($thispage < $pages) {
		$output .= '<a href="pager.php?firstresult=' . ($firstresult + $perpage) . '">Next page</a>';
	}

At the very end of our function we echo $output

echo $output;

The Complete Function

function paginate($perpage,$table,$field) {	
 
// define from which record to start the query from in the LIMIT function.
if (isset($_GET['firstresult'])) {
	$firstresult = (int) ($_GET['firstresult']);
	} else {
	$firstresult = 0;
	}
 
//count how many rows in the database and assign the result to $count
$countquery = "SELECT COUNT(*) FROM `$table`";
$countarray = mysql_fetch_array(mysql_query($countquery));
$count = $countarray[0];
 
//how many pages will be needed to display all records
$pages = ceil($count/$perpage);
 
//pull results from database for this page
$query = "SELECT * FROM `$table` LIMIT $firstresult,$perpage";
$results = mysql_query($query);
 
//produce a html list showing the results
$output = "<ul>\n";
while ($list = mysql_fetch_assoc($results)) {
	$output .= '<li>' . $list[$field] . '</li>';
}
$output .= "</ul>\n";
 
// check to see if 'next' and 'previous' links are needed and create
// numbered links to other pages.
if ($pages > 1) {
	$thispage = ($firstresult/$perpage) + 1;
	$output .= "<p>Page $thispage of $pages</p>";
	if ($thispage > 1) {
		$output .= '<a href="pager.php?firstresult=' . ($firstresult - $perpage) . '">Previous page</a>';
	}
	$output .= '<p>';
	for ($i = 1; $i <= $pages;$i++) {
		$output .= '<a href="pager.php?firstresult=' . ($perpage * ($i - 1)) . '">'.$i.'</a>';
	}
	$output .= '</p>';
	if ($thispage < $pages) {
		$output .= '<a href="pager.php?firstresult=' . ($firstresult + $perpage) . '">Next page</a>';
	}
}
echo $output;
}

And that’s that. To use the function, you would have something like this in your page:

//connect to your database here
include_once('paginate.php');
paginate(5,'news','title'); // for example

Category: PHP // Make ElanMan happy and be the first to comment.Open my Lovebox

RSS

ElanMan