The SELECT statement in SQL is a simple but powerful one. A single SELECT query can return thousands of results. When building blogs and websites, you usually don’t want to display thousands of results on a single page. This would impact negatively on page load time and create a bad user experience.

To solve the problem of displaying many database records on a website, the standard best practice is to split the results across multiple web pages. This is called pagination. And this article will explain how to do this using PHP and MySQL.

For the sake of beginners, we will be using procedural code in our examples. You can make a class out of the snippets you find here if you write object-oriented PHP.

How Pagination Works

• First, using an SQL query, the total number of records to be displayed is calculated.
• Next, using MySQL’s LIMIT clause, starting and ending points are defined in the SQL query.

Let’s write the code for each of these points and explain the blocks of code.

Our first piece of code will use a pagenum URL parameter. This will help us ascertain what page the user is on. So, our URL will be something like http://example.com/people.php?pagenum=2

The pagenum parameter has to be passed in the URL to let the PHP script know what page number is being processed. The following snippet illustrates how the page number works in the script.

if (isset($_GET['pagenum'])) {
	$pagenum = $_GET['pagenum'];
} else {
	$pagenum = 1;
}

First, we check to verify if the pagenum parameter is passed to the URL. If it is present in the URL, a variable ($pagenum) is created to store the value of the parameter.

If the parameter has not been passed, the variable is still created, but its value is set to 1. This way, if the URL doesn’t have the parameter you checked for in the script, the query returns the first set of records in your database table.

The next step is to create another variable to store the total number of records to be displayed per page. This will serve as the “end” value for the LIMIT clause in the MySQL SELECT query. We will also create another variable to hold the “start” value of the LIMIT clause for the query. Here’s the snippet:

$recordsPerPage = 20;
$start = ($pagenum-1) * $recordsPerPage;

To get the total number of pages, we first get the total number of records we want to select, and then we divide that figure by the number of records per page.

For this to work properly, we will use the PHP ceil() function. In the event that the result of our above division is a float, our ceil() function will help round the result of the division up to the nearest higher integer.

Here’s how it is done:

$getTotalRecords = "SELECT COUNT(*) FROM tableName";
$result = mysqli_query($conn, $getTotalRecords);
$totalRecords = mysqli_fetch_array($result)[0];
$totalPages = ceil($totalRecords / $recordsPerPage);

Our SQL query for the pagination would look like:

SELECT * FROM tableName LIMIT $start, $totalPages;

Now, the core functionality has been set up. We need to set up the buttons and make sure they work properly. The buttons we will use are FIRST, PREVIOUS, NEXT and LAST. To simplify things and help with CSS styling, we will be using Bootstrap which has a built-in class for pagination. The Bootstrap stuff isn’t shown here.

<ul class="pagination">
	<li>
		<a href="?pagenum=1">First</a>
	</li>
	<li class="<?php if($pagenum <= 1){ echo 'disabled'; } ?>">
		<a href="<?php if($pagenum <= 1){ echo '#'; } else { echo "?pageno=".($pagenum - 1); } ?>">Prev</a>
	</li>
	<li class="<?php if($pagenum >= $totalPages){ echo 'disabled'; } ?>">
		<a href="<?php if($pagenum >= $totalPages){ echo '#'; } else { echo "?pagenum=".($pagenum + 1); } ?>">Next</a>
	</li>
	<li>
		<a href="?pagenum=<?php echo $totalPages; ?>">Last</a>
	</li>
</ul>

In the snippet above, the button labelled FIRST links to the first page of results.

For the button labelled PREV, there is some logic. If the current page is the first page, the CSS class is set to “disabled” using PHP. That way, a user cannot click it.

If the page is the first, there is no result to be displayed if the button is clicked. So, the link is disabled by assigning “#” as the value of the HREF attribute. If the user is NOT on the first page, the pagenum attribute is decremented by one and passed to the URL.

For the NEXT button, we reverse the logic applied to the PREV button. And for the LAST button, we place the variable holding the value of the total number of pages as the value of the pagenum parameter to be passed to the URL.

The whole script is dumped below, including basic markup for the page. You can copy and paste all the code below and tweak a few things. The comments in the code indicate what to tweak.

PHP Pagination Code:

<?php
	if (isset($_GET['pagenum'])) {
		$pagenum = $_GET['pagenum'];
	} else {
		$pagenum = 1;
	}
	$recordsPerPage = 10;
	$start = ($pagenum - 1) * $recordsPerPage;
	$conn = mysqli_connect("host", "user", "pass", "dbname"); //adjust the connection parameters to work with your dev environment.
	
	// Check connection
	if (mysqli_connect_errno()) {
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		die();
	}
	$fetchPages = "SELECT COUNT(*) FROM tableName"; //change "tableName" to your table name.
	$result = mysqli_query($conn, $fetchPages);
	$totalRecords = mysqli_fetch_array($result)[0];
	$totalPages = ceil($totalRecords / $recordsPerPage);
	$sql = "SELECT * FROM state LIMIT $start, $recordsPerPage";
	$res_data = mysqli_query($conn, $sql);
	while ($row = mysqli_fetch_array($res_data)) {
		echo "<li>" . $row['name'] . "</li>"; //change 'name' to the appropriate offset from your database table.
	}
	mysqli_close($conn);
?>

HTML Pagination Code:

<ul class="pagination">
	<li>
		<a href="?pagenum=1">First</a>
	</li>
	<li class="<?php if ($pagenum <= 1) { echo 'disabled'; } ?>">
		<a href="<?php if ($pagenum <= 1) { echo '#'; } else { echo "?pagenum=" . ($pagenum - 1); } ?>">Prev</a>
	</li>
	<li class="<?php if ($pagenum >= $totalPages) { echo 'disabled'; } ?>">
		<a href="<?php if ($pagenum >= $totalPages) { echo '#'; } else { echo "?pagenum=" . ($pagenum + 1); } ?>">Next</a>
	</li>
	<li>
		<a href="?pagenum=<?php echo $totalPages; ?>">Last</a>
	</li>
</ul>

You can make changes to the code above and extend it as far as your imagination and skills can take you.