applite_admin

Pagination in PHP and MySQL

pagination
Pagination is one of the common features of any site that displays many rows of data.  It seems easy, but without proper planning, it either won’t work, or won’t be efficient.

It’s not unusual to run across someone’s code that pulls all results from a table while using only a subset of data that looks paginated, but isn’t.  It’s hardly noticeable if you have 100 rows and paginate 20 results at a time… but it gets out of hand when thousands of rows or more are pulled each time.

In this guide, we will go through the planning and implementation steps of building a PHP/MySQL (or any SQL) pagination solution.  Just want the code?  Click here.

Prerequisites:

  • Running PHP and MySQL 4+ instance. This guide was done using MariaDB 10.x, and PHP 7.x.  MySQL 4+ is required to take advantage of SQL_CALC_FOUND_ROWS functionality.
  • Access to MySQL, read access to DB: mysql, and table mysql.help_keyword.
  • Basic knowledge of PHP and MySQL.

Overview

For the sake of simplicity, we will use procedural style PHP.  Anyone proficient in Object Oriented PHP will have no problem converting the example.  Our data is also simple, it’s using the mysql.help_keyword table that exists in modern installations of MySQL.

mysql.help_keyword table structure – as simple as it gets!

MariaDB [mysql]> desc help_keyword;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| help_keyword_id | int(10) unsigned | NO   | PRI | NULL    |       |
| name            | char(64)         | NO   | UNI | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

The HTML in our example is simple, valid, and ugly.  It will display some status information, a result set, and paging navigation links.

Pagination variables

All pagination implementations that exist use some variation of the following variables.  We’ll use descriptive names to make it easier to follow along.

  • $ITEMS_STEPS – An array of values that will be used for the user’s selection of $items_per_page.  The first item is the default number of items per page.  This list also prevents user’s from modifying the query string to put arbitrary values in $items_per_page.
  • $items_per_page – The number of items to display on the page.  There should always be a default for this value, and you might want to allow the user to select a different value.  On a mobile device, it might be fine to display 10 items per page, but on a 4k monitor, they could easily desire 100 items per page.  Our example’s default will be 20 items per page.
  • $current_page – The current page being viewed.  We’ll assume the default first page is 0.
  • $total_pages                 – The total pages and the last page of results.  This is used to display a link for the last page of results, should a user want to skip all the way to the end.  It’s also handy to know this value to prevent your links from displaying a “Next >” link, when there are no more pages.  Total pages is roughly your $total_sql_results / $items_per_page.
  • $total_sql_results – The total number of items in the result set.  In this case, mysql.help_keyword has 464 items.
  • $item_sql_start – Where the result starts.  For example, $current_page=0 would start at result 0, $current_page=1 starts at result 21, $current_page=2 starts at result 41, and so on.

$items_per_page, and $current_page are visible to the user and present in the query string ($_GET).  We won’t cover security implications here, but it is important to prevent arbitrary numbers in $items_per_page to avoid a potential attack vector.  Specifically, you do not want someone being able to query the whole possible result set, and do it with multiple requests.

SQL_CALC_FOUND_ROWS or not?

Since MySQL 4.0, functionality exists to obtain the total number of results from a query, without actually pulling all results.  This is useful for queries with the LIMIT clause where you also need the total count without LIMIT.  Before this, one had to run the count query and main query to obtain a total count and your data.

Some old documentation does highlight a performance hit with SQL_CALC_FOUND_ROWS, but it’s much faster over 10 years after its introduction.  You can still run the separate count query, the only hassle there is making sure to maintain 2 query definitions.  The biggest factor in speed may be your schema design and the indexes.

Two-query method:

MariaDB [mysql]> SELECT * FROM help_keyword LIMIT 20, 20;
+-----------------+-------------------------------+
| help_keyword_id | name                          |
+-----------------+-------------------------------+
|              20 | MASTER_SSL_VERIFY_SERVER_CERT |
<snipped>

MariaDB [mysql]> SELECT COUNT(*) FROM help_keyword;
+----------+
| COUNT(*) |
+----------+
|      464 |
+----------+
1 row in set (0.00 sec)

Single-query (SQL_CALC_FOUND_ROWS ) method:

MariaDB [mysql]> SELECT SQL_CALC_FOUND_ROWS * FROM help_keyword LIMIT 20, 20;
+-----------------+-------------------------------+
| help_keyword_id | name                          |
+-----------------+-------------------------------+
|              20 | MASTER_SSL_VERIFY_SERVER_CERT |
<snipped>

MariaDB [mysql]> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          464 |
+--------------+
1 row in set (0.00 sec)

The last SELECT statement is not running a new query, it’s simply returning the stored result count from your first query.

Building the query

The MySQL LIMIT clause is most commonly used to limit the number of results in a query.  When we use “LIMIT 20”, it means to only return 20 results.  To page, we might use “LIMIT 100, 20”, which means to start returning results at the 100th result, and return 20 results from that point.  Modification of these parameters for LIMIT is the key to our pagination.

Another way to think about it:
SELECT * FROM <sometable> WHERE this=that LIMIT <start result>, <items per page>

Page Links

We will have to generate HTML that allows navigation of the pages.  Below is a description of how those links are determined.  You also have to factor in whether or not these navigation pages exist, such as the first page not having a “Previous Page”.

First                       – Internally, we will refer to page 0 as the first page.  The actual link on a website will say page 1 because… well, we rarely see anything referred to as the 0th place in the real world.  The first page is equivalent to: LIMIT <items per page>

Previous              – Current page minus 1

Next                      – Current page plus 1

Last                        – Total pages (The total is generally the last page, but might not be if there’s a division remainder)

Page #                  – Numbered pages follow the logic of being between the First Page and Last Page.  There are many ways to implement this depending on your user experience needs.  In our example, we only display this if there’s 8 or more pages, and display 4 page counts at the start, and 4 page counts at the end.

Putting the code together

Replace the MySQL credentials with your own, and enjoy!
If you prefer, get the code from GitHub.

<?PHP
/**
 * Pagination Sample using the MySQL table mysql.help_keyword
 *
 *	This file is meant to teach the basics using a standalone file, so the bells & whistles are
 *	missing and the it is procedural.
 *
 * (C) 2018 Marc Stephenson / GitHub: user27828
 */
error_reporting(E_ALL);
// For sample purposes only!  It is a bad idea to store credentials in every other file.
$link = mysqli_connect(<HOST>, <USERNAME>, <PASSWORD>, 'mysql');

// Variable assignments
//-------------------------------------------------------------------
// Selection options for $items_per_page.  The user will see links to select these options for "Items per page".
$ITEMS_STEPS        = array(10, 20, 50, 100, 200, 400, 1000);
 
/* For page number links - pages at the beginning, and pages at the end.
 *  Your implementation might automatically generate the pages based on
 *  any factors you decide.
 */
$pages_per_direction = 4;

/* Items per page - check for the query string var "items_per_page", otherwise, assign a default.
 * The default value will be the first value in $ITEMS_STEPS
 */
$items_per_page     = (array_key_exists('items_per_page', $_GET) && !empty($_GET['items_per_page']) )
	? intval($_GET['items_per_page']) : $ITEMS_STEPS[0];

/* An end-user might try to use their own value for &items_per_page=...
 * if it's not a value listed in $ITEMS_STEPS, then reject it, set 
 * $items_per_page to the default.  This prevents someone a scenario 
 * such as: setting items to a billion on a query that may have a billion 
 * results (could easily turn into a DoS vulnerability).
 */
if( !in_array($items_per_page, $ITEMS_STEPS) ) {
	$items_per_page     = $ITEMS_STEPS[0];
	// Redirect to the default page with default items per page.
	header(sprintf('Location: %s?items_per_page=%d', $_SERVER['PHP_SELF'],  $items_per_page));
	exit;
}

// The query variable appended to links if "items_per_page" is set in query string
$items_per_page_qry = (array_key_exists('items_per_page', $_GET) && !empty($_GET['items_per_page']) )
	? sprintf('&amp;items_per_page=%d', $items_per_page) : NULL;

/* Current page number - default is 0 (first page).  For the end-user, 
 * links and text will display the page as $current_page+1.  This is 
 * because no normal person reads the first page as page 0.
 * Strict user variable checking isn't so important here, if the result 
 * LIMIT is overshot, then it's only an empty result.
 */
$current_page       = (array_key_exists('page', $_GET) && !empty($_GET['page']) )
	? intval($_GET['page']) : 0;

// First param in SQL LIMIT clause, based on current page * items per page
$item_sql_start     = $current_page * $items_per_page;

// SQL and related variable assignment
//-------------------------------------------------------------------
// Define the LIMIT clause.  If there's no page params, just limit from the beginning by items per page
$query_limit        = !empty($item_sql_start) 
    ? sprintf('LIMIT %d, %d', $item_sql_start, $items_per_page) : sprintf('LIMIT %d', $items_per_page);
$query              = sprintf('SELECT SQL_CALC_FOUND_ROWS * FROM help_keyword %s', $query_limit);
$result             = mysqli_query($link, $query);
$TOTAL              = mysqli_fetch_array(mysqli_query($link, 'SELECT FOUND_ROWS()'));
$total              = intval($TOTAL['FOUND_ROWS()']);	// Total results, populated via SQL_CALC_FOUND_ROWS/FOUND_ROWS()
$total_modulo       = $total % $items_per_page;			// Modulo is the division remainder of $a/$b
// Total pages/last page - If modulo has a remainder, add an additional page
$total_pages        = ($total_modulo==0) ? $total/$items_per_page : ceil($total/$items_per_page);


// Some basic HTML/CSS
//-------------------------------------------------------------------
print '<!DOCTYPE html><html lang="en-US" class="no-js"><head><title>Pagination Test</title><style>
a { margin: 0 5px; }
.debug { width: 700px; border-bottom: 1px dotted #00F; }
.debug strong { display: inline-block; width: 250px; }
</style></head></body>';
 
// Debugging data display
//-------------------------------------------------------------------
print '<pre><div style="border: 1px dashed #000;"><strong>Debug Data</strong><br />';
printf('<div class="debug"><strong>Items Per Page:</strong> %s</div>',		$items_per_page);
printf('<div class="debug"><strong>Current Page: </strong> %s</div>',		$current_page+1);	// Front end/human readable page#
printf('<div class="debug"><strong>SQL Start item:</strong> %s</div>',		$item_sql_start);
printf('<div class="debug"><strong>SQL Query:</strong> %s</div>',			$query);
printf('<div class="debug"><strong>Total SQL results:</strong> %s</div>',	$total);
printf('<div class="debug"><strong>Total/Last Page:</strong> %s <em>(floor|ceil(total/per page))</em></div>', $total_pages);
print '</div><p>&nbsp;</p>';
 
// DB Result display
//-------------------------------------------------------------------
if( $result ) {
	// Display the SQL results that utilize pagination limits
	print "Count\t| Result Count\t| help_keyword_id\t| name\n"; // Columns - the last 2 are from DB
	print "--------------------------------------------------------------------------\n";
	$i  = 0;
	while( $ROW = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
		$i++;
		printf("%02d\t| %03s\t\t| %s\t\t\t| %s\n", $i, $item_sql_start+$i, $ROW['help_keyword_id'], $ROW['name'] );
	}
	print "--------------------------------------------------------------------------\n";
	print '<p><strong>Notes: </strong> "Count" is the <strong>current</strong> result set, it will only go up to $items_per_page.
	"help_keyword_id" is unrelated to the counts, any similarity to counts is coincidental.</p>';
} else {
	print 'Error - Could not fetch results using query: ' . $query;
	exit;
}
mysqli_free_result($result);
mysqli_close($link);
 
print '</pre>';
 
// Navigation links - Items Per Page & First/Previous/<steps>/Next/Last
//-------------------------------------------------------------------
$LINKS              = array();
foreach($ITEMS_STEPS as $step) {
	// Items per page navigation
	$LINKS[]            = ($step!=$items_per_page) 
		? sprintf('[<a href="%s?items_per_page=%d">%d</a>]', $_SERVER['PHP_SELF'], $step, $step)
		: $step;
}
printf('<p>Items Per Page: %s</p>', implode('&nbsp;|&nbsp;', $LINKS));
 
// First page
printf('<a href="%s?page=0%s">[&lt;&lt;First Page]</a>', $_SERVER['PHP_SELF'], $items_per_page_qry);
if( !empty($current_page) ) {
	printf('<a href="%s?page=%d%s">[&lt;Previous Page]</a>', $_SERVER['PHP_SELF'], $current_page-1, $items_per_page_qry);
}
 
/* Display links to specific page numbers IF we have a sufficient number of pages.
 *  There are many ways to implement this.  The code below is very simple and does not
 *  automatically adjust to the result set.
 */
if( $total_pages > ($pages_per_direction*2) ) {
	// Pages at the beginning
	for($i=1; $i<=$pages_per_direction; $i++) {  // Page "1" is the second page of results
		printf('<a href="%s?page=%d%s">(%d)</a>', $_SERVER['PHP_SELF'], $i-1, $items_per_page_qry, $i);
	}
	 
	print '&nbsp...&nbsp;'; // Spacing between beginning and end links
	 
	// Pages at the end
	for($i=($total_pages-$pages_per_direction); $i<$total_pages+1; $i++) {
		printf('<a href="%s?page=%d%s">(%d)</a>', $_SERVER['PHP_SELF'], $i-1, $items_per_page_qry, $i);
	}
}
 
// Hide the Next/Last page if we only have 1 total page
if( $total_pages > 1 ) {
	// Don't show "Next Page" if there is no next page
	if( $current_page<$total_pages ) {
		printf('<a href="%s?page=%d%s">[Next Page&gt;]</a>', $_SERVER['PHP_SELF'], $current_page+1, $items_per_page_qry);
	}
	 
	// Last Page
	printf('<a href="%s?page=%d%s">[Last Page&gt;&gt;]</a>', $_SERVER['PHP_SELF'], $total_pages-1, $items_per_page_qry);
}
 
print '<p>&nbsp;</p></body></html>';
?>