Pagination in PHP and MySql: Simplify Your Data Display and Navigation

pagination in php

Mastering Pagination in PHP: A Comprehensive Guide

Are you a web developer looking to enhance the user experience on your website by implementing efficient pagination? Look no further! In this comprehensive guide, we will delve into the world of pagination in PHP and equip you with the knowledge and skills to optimize your website’s performance.

Understanding Pagination and its Importance

Pagination plays a crucial role in organizing and presenting large sets of data on a website. It divides content into manageable chunks, allowing users to navigate through pages without overwhelming them with excessive information. Proper implementation of pagination not only improves user experience but also enhances the website’s performance and search engine optimization (SEO) ranking.

The Basics of Pagination in PHP

To get started with pagination in PHP, it’s essential to understand the fundamental concepts and techniques involved. Here are the key steps to follow:

Step 1: Gathering Data

Before implementing pagination, you need to retrieve the data from your database. This typically involves executing a database query using PHP, fetching the relevant data, and storing it in a variable.

Step 2: Determining the Number of Results per Page

To ensure optimal user experience, you must decide how many results to display on each page. Factors to consider include the nature of the content and the preferences of your target audience. You can experiment with different numbers and analyze user behavior to find the right balance.

Step 3: Calculating the Total Number of Pages

Once you have the total number of results and the desired results per page, you can calculate the total number of pages. This can be done by dividing the total number of results by the results per page and rounding up to the nearest integer.

Step 4: Handling the Current Page

To implement pagination in php, you need to determine which page the user is currently on. This can be achieved by capturing the page number from the URL or using a query parameter. It’s crucial to validate and sanitize the input to prevent potential security risks.

Step 5: Displaying the Paginated Data

Based on the current page, you can determine the range of results to display. This can be achieved by calculating the starting and ending indexes for the data array or by modifying your database query with appropriate LIMIT and OFFSET clauses.

To enable users to navigate through the pages, you should generate pagination links. These links typically include the page number and are often styled using CSS to provide a visually appealing and user-friendly interface. The links should update dynamically based on the current page, the total number of pages, and any other relevant factors.

Best Practices for Effective Pagination

To maximize the benefits of pagination, it’s important to follow some best practices. Here are a few tips to consider:

1. Efficient Database Queries

Optimize your database queries to retrieve only the necessary data for each page. Avoid fetching all the results and then filtering them later, as this can significantly impact performance.

2. SEO-friendly URLs

Make sure your pagination URLs are SEO-friendly and include relevant keywords. This can help search engines understand the structure of your website and improve its ranking in search results.

3. Mobile Responsiveness

Design your pagination in php system to be responsive and adaptable to different screen sizes. With the increasing use of mobile devices, it’s crucial to provide a seamless experience across all platforms.

4. Usability Testing

Regularly test your pagination implementation to ensure it meets the expectations of your users. Pay attention to factors such as load times, ease of navigation, and overall user satisfaction.

In PHP, adding pagination to your website is a relatively straightforward process. Here’s an example code that demonstrates how to implement a basic pagination in php system:

Create Database:


Create student table:

CREATE TABLE student (
  name VARCHAR(30) NOT NULL,
  age INT(3) NOT NULL,
  joined DATE NOT NULL

This is a PHP code that handles pagination for a database table named “students”. Here’s a brief explanation of how it works:

  • The first line connects to the MySQL server using the credentials ‘localhost’, ‘root’, ” and ‘pagination’ (database name). This step is optional if you have already connected to your database elsewhere.
  • Next, it runs a SQL query to count the total number of records in the “students” table and assigns it to the variable $total_pages.
  • The page number is then checked by accessing the $_GET superglobal array. If the requested page number is valid (numeric), it is assigned to the variable $page. Otherwise, the default value of 1 is used.
  • The variable $num_results_on_page controls the number of results to display per page.
  • A prepared statement is then prepared to select the target page of data from the students table with ORDER BY and LIMIT clauses based on the current page number ($page) and the number of results to show per page ($num_results_on_page).
  • The actual page to load is calculated using the formula ($page – 1) * $num_results_on_page, which determines which records to display. The bind_param method binds the calculated page and the number of results to show per page to the prepared statement.
  • The execute() function executes the SELECT statement and the get_result() method retrieves the result set as a mysqli_result object.

Create PHP file index.php

// Below is optional, remove if you have already connected to your database.
$mysqli = mysqli_connect('localhost', 'root', '', 'pagination');

// Get the total number of records from our table "students".
$total_pages = $mysqli->query('SELECT * FROM students')->num_rows;

// Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;

// Number of results to show on each page.
$num_results_on_page = 5;

if ($stmt = $mysqli->prepare('SELECT * FROM students ORDER BY name LIMIT ?,?')) {
	// Calculate the page to get the results we need from our table.
	$calc_page = ($page - 1) * $num_results_on_page;
	$stmt->bind_param('ii', $calc_page, $num_results_on_page);
	// Get the results...
	$result = $stmt->get_result();
	<!DOCTYPE html>

		<title>PHP & MySQL Pagination by CodeShack</title>
		<meta charset="utf-8">
			html {
				font-family: Tahoma, Geneva, sans-serif;
				padding: 20px;
				background-color: #F8F9F9;

			table {
				margin-top: 20px;
				border-collapse: collapse;

			th {
				padding: 10px;

			th {
				background-color: #54585d;
				color: #ffffff;
				font-weight: bold;
				font-size: 13px;
				border: 1px solid #54585d;

			td {
				padding: 10px;
				text-align: center;
				border-bottom: 1px solid #ddd;

			tr {
				background-color: #f9fafb;

			tr:nth-child(odd) {
				background-color: #ffffff;

			.pagination {
				list-style-type: none;
				padding: 10px 0;
				justify-content: space-between;
				box-sizing: border-box;
				display: flex;
				justify-content: center;

			.pagination li {
				box-sizing: border-box;
				padding-right: 10px;

			.pagination li a {
				box-sizing: border-box;
				background-color: #e2e6e6;
				padding: 8px;
				text-decoration: none;
				font-size: 12px;
				font-weight: bold;
				color: #616872;
				border-radius: 4px;

			.pagination li a:hover {
				background-color: #d4dada;

			.pagination .next a,
			.pagination .prev a {
				text-transform: uppercase;
				font-size: 12px;

			.pagination .currentpage a {
				background-color: #518acb;
				color: #fff;

			.pagination .currentpage a:hover {
				background-color: #518acb;

		<div style="display:flex; justify-content:center;">
		<table style="width: 70%;" >
				<th>Join Date</th>
			<?php while ($row = $result->fetch_assoc()) : ?>
					<td><?php echo $row['name']; ?></td>
					<td><?php echo $row['age']; ?></td>
					<td><?php echo $row['joined']; ?></td>
			<?php endwhile; ?>
		<?php if (ceil($total_pages / $num_results_on_page) > 0) : ?>
			<ul class="pagination">
				<?php if ($page > 1) : ?>
					<li class="prev"><a href="index.php?page=<?php echo $page - 1 ?>">Prev</a></li>
				<?php endif; ?>

				<?php if ($page > 3) : ?>
					<li class="start"><a href="index.php?page=1">1</a></li>
					<li class="dots">...</li>
				<?php endif; ?>

				<?php if ($page - 2 > 0) : ?><li class="page"><a href="index.php?page=<?php echo $page - 2 ?>"><?php echo $page - 2 ?></a></li><?php endif; ?>
				<?php if ($page - 1 > 0) : ?><li class="page"><a href="index.php?page=<?php echo $page - 1 ?>"><?php echo $page - 1 ?></a></li><?php endif; ?>

				<li class="currentpage"><a href="index.php?page=<?php echo $page ?>"><?php echo $page ?></a></li>

				<?php if ($page + 1 < ceil($total_pages / $num_results_on_page) + 1) : ?><li class="page"><a href="index.php?page=<?php echo $page + 1 ?>"><?php echo $page + 1 ?></a></li><?php endif; ?>
				<?php if ($page + 2 < ceil($total_pages / $num_results_on_page) + 1) : ?><li class="page"><a href="index.php?page=<?php echo $page + 2 ?>"><?php echo $page + 2 ?></a></li><?php endif; ?>

				<?php if ($page < ceil($total_pages / $num_results_on_page) - 2) : ?>
					<li class="dots">...</li>
					<li class="end"><a href="index.php?page=<?php echo ceil($total_pages / $num_results_on_page) ?>"><?php echo ceil($total_pages / $num_results_on_page) ?></a></li>
				<?php endif; ?>

				<?php if ($page < ceil($total_pages / $num_results_on_page)) : ?>
					<li class="next"><a href="index.php?page=<?php echo $page + 1 ?>">Next</a></li>
				<?php endif; ?>
		<?php endif; ?>


This code creates a pagination in php component to display page navigation links for the “students” table. Here’s how it works:

  • The first line checks if there are any pages to display by checking if the total number of pages divided by the number of results per page is greater than zero.
  • If there are pages to display, an unordered list with class “pagination” is created.
  • The “Prev” link is displayed if the current page ($page) is greater than 1.
  • If the current page is greater than 3, a “1” link and ellipsis are displayed to go back to the first page.
  • If the current page minus 2 is greater than 0, display a link to this page. Similarly if current page minus 1 is greater than 0, display a link to this page.
  • A “current” class is applied to the currently active page to style it differently.
  • If the current page plus 1 is less than the total number of pages to be displayed plus 1 (because we add 1 to ceil($total_pages / $num_results_on_page) in the conditionals to account for floating point rounding), display a link to this page. Similarly if the current page plus 2 is less than ceil($total_pages / $num_results_on_page) + 1, display a link to this page.
  • If the current page is less than the total number of pages minus 2, display an ellipsis and a “last” page link.
  • The “Next” link is displayed if the current page is less than the total number of pages.

The echo statements populate the href attributes of each link based on the value of $page.


Pagination is a vital component of any website that deals with large amounts of data. By mastering pagination techniques in PHP, you can significantly enhance user experience, improve website performance, and boost your SEO ranking. Remember to follow best practices of pagination in php, stay updated with the latest trends, and continuously optimize your pagination implementation. Happy coding!

Output image of pagination in php:

pagination in php

Our Recommendation

Avatar of Akhand Pratap Singh

Akhand Pratap Singh

Greetings and a warm welcome to my website! I am Akhand Pratap Singh, a dedicated professional web developer and passionate blogger.

Related Post

Leave a Comment


Subscribe for latest updates

We don't spam.