HTML CSS JavaScript jQuery PHP MySQL

How to Create Pagination with PHP and MySQL

Updated on by David Adams

How to Create Pagination with PHP and MySQL

For this tutorial we will be creating pagination with PHP and MySQL, pagination is how users will navigate certain parts of your website that contains large amounts of data, we will be using MySQL in this tutorial, we will select records from a database and display them in a table using HTML and PHP with pagination.

We will display the next and previous buttons in this pagination, as well as the first and last pages, and the pages in between with the current page being highlighted.

We need to do a bit more work than just showing the next and previous buttons, but this is because we can change the code, change it to how we want it, if we want to remove the pagination pages we can do so.


Pagination.php

Let's go ahead and create the pagination file, we will call this pagination.php.

We now need to connect to our database, we can do so with the following line of code:

$mysqli = mysqli_connect('localhost', 'root', '', 'pagination');

You can change the connection information, replace the text with your MySQL details: mysqli_connect(YOUR_HOST, YOUR_USERNAME, YOUR_PASSWORD, YOUR_DATABASE)

For the purpose of this tutorial I have created the database pagination, below is the SQL statement to this database, run the SQL statement with phpMyAdmin, or your preferred database management tool.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE DATABASE IF NOT EXISTS `pagination` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `pagination`;

CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` tinyint(100) NOT NULL,
  `joined` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;

INSERT INTO `students` (`id`, `name`, `age`, `joined`) VALUES
(1, 'David Deacon', 23, '17/08/2018'),
(2, 'Sheri Warner', 19, '03/05/2018'),
(3, 'Sean Glover', 24, '24/07/2018'),
(4, 'John West', 17, '13/08/2018'),
(5, 'Rufus Clarke', 20, '28/07/2018'),
(6, 'Roosevelt Myers', 20, '25/07/2018'),
(7, 'Elvira Andrews', 22, '02/07/2018'),
(8, 'Richard	Cook', 26, '19/07/2018'),
(9, 'Lorenzo	Harris', 23, '01/07/2018'),
(10, 'Eduardo	Hoffman', 17, '03/07/2018'),
(11, 'Jeanne Fisher', 20, '13/08/2018'),
(12, 'Tracy Bowers', 30, '07/07/2018'),
(13, 'Heidi Lawrence', 18, '04/06/2018'),
(14, 'Tara Holland', 25, '01/07/1991'),
(15, 'Grant Edwards', 22, '22/06/2018'),
(16, 'Bradford Green', 29, '02/05/2018'),
(17, 'Gwen Schultz', 20, '02/05/2018'),
(18, 'Hope Dawson', 28, '21/08/2018'),
(19, 'Florence Osborne', 19, '17/05/2018'),
(20, 'Rickey Poole', 26, '28/06/2018'),
(21, 'Casey Sutton', 28, '06/07/2018'),
(22, 'Willie Lowe', 23, '11/05/2018'),
(23, 'Stephen	Schultz', 28, '15/07/2018'),
(24, 'Eileen Lynch', 18, '12/06/2018'),
(25, 'Aaron Ruiz', 29, '02/05/2018'),
(26, 'Mae Murray', 30, '24/06/2018'),
(27, 'Regina Hanson', 21, '26/07/2018'),
(28, 'Cameron	Mclaughlin', 20, '29/07/2018'),
(29, 'Earl Hale', 17, '30/06/2018'),
(30, 'Marta Blair', 24, '10/06/2018'),
(31, 'Alberta	Silva', 22, '05/06/2018'),
(32, 'Joanna Holmes', 20, '20/05/2018'),
(33, 'Alex Brock', 30, '12/05/2018'),
(34, 'Colin Wright', 19, '28/05/2018'),
(35, 'Peter Schmidt', 25, '10/07/2018'),
(36, 'Joshua Price', 27, '13/07/2018'),
(37, 'Elias Chandler', 22, '19/07/2018'),
(38, 'Stanley	Ross', 21, '02/06/2018'),
(39, 'Vera Cole', 26, '02/05/2018'),
(40, 'Johnny Daniels', 29, '19/07/2018'),
(41, 'Yvonne Hopkins', 21, '16/07/2018');

ALTER TABLE `students` ADD PRIMARY KEY (`id`);

ALTER TABLE `students` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;

Let's get back to the pagination PHP file, what we need to do now is to create some important variables that we will use for the 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;

With the first variable we are getting the total number of rows from the students table in our database, this will be used to calculate how many pages there will be. The second variable is used to determine which page the user is on, for example, if the URL is pagination.php?page=3 then the page number will be 3, if the page doesn't exist in the URL it will default to 1. The third variable determines how many results will be shown on each page, for example, if we wanted to show 10 results (list of 10 students) we will change the number to 10, the default is set to 5.

So now we need to get the records from the database for the current page, the following code below will do just that.

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);
	$stmt->execute(); 
	// Get the results...
	$result = $stmt->get_result();
	$stmt->close();
}

There are a few things that are going on in the above code, the first thing we do is prepare the statement, this prevents SQL injection, if you do not do this you may be at risk of being hacked, the $calc_page variable basically determines the start index in our table, after, we bind the variables (i is for integer), and then execute the SQL statement. The result will be stored in the $result variable.

Now we need to display these results, the results will be displayed in a table, you can display the results how you want but for this tutorial, we will use a table.

<table>
	<tr>
		<th>Name</th>
		<th>Age</th>
		<th>Join Date</th>
	</tr>
	<?php while ($row = $result->fetch_assoc()): ?>
	<tr>
		<td><?php echo $row['name']; ?></td>
		<td><?php echo $row['age']; ?></td>
		<td><?php echo $row['joined']; ?></td>
	</tr>
	<?php endwhile; ?>
</table>

The above code will basically display each student in the table with their name, age, and joined date.

Now we need to display the pagination.

<?php if (ceil($total_pages / $num_results_on_page) > 0): ?>
<ul class="pagination">
	<?php if ($page > 1): ?>
	<li class="prev"><a href="pagination.php?page=<?php echo $page-1 ?>">Prev</a></li>
	<?php endif; ?>

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

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

	<li class="currentpage"><a href="pagination.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="pagination.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="pagination.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="pagination.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="pagination.php?page=<?php echo $page+1 ?>">Next</a></li>
	<?php endif; ?>
</ul>
<?php endif; ?>

The first thing we do here is to check if there are more than 1 page, no need to display the pagination if there is only 1 page. We then check if the current page is greater than 1, if it is, show the previous button, and then if the page is greater than 3 we can show the first page, which is 1.

After that we display the 2 pages before and after the current page, so if the page is 5 it will be displayed like this 1 ... 3 4 5 6 7 ... 14, it's basically the same as we have done after that but in reverse.

Now we need to add some style to our pagination and table, the following CSS code will do just that.

table {
	border-collapse: collapse;
	width: 500px;
}
td, th {
	padding: 10px;
}
th {
	background-color: #54585d;
	color: #ffffff;
	font-weight: bold;
	font-size: 13px;
	border: 1px solid #54585d;
}
td {
	color: #636363;
	border: 1px solid #dddfe1;
}
tr {
	background-color: #f9fafb;
}
tr:nth-child(odd) {
	background-color: #ffffff;
}
.pagination {
	list-style-type: none;
	padding: 10px 0;
	display: inline-flex;
	justify-content: space-between;
	box-sizing: border-box;
}
.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;
}

You should now have something that will look like this:

PHP Pagination Tutorial Design

Source

The full pagination.php source:

<?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);
	$stmt->execute(); 
	// Get the results...
	$result = $stmt->get_result();
	?>
	<!DOCTYPE html>
	<html>
		<head>
			<title>PHP & MySQL Pagination by CodeShack</title>
			<meta charset="utf-8">
			<style>
			html {
				font-family: Tahoma, Geneva, sans-serif;
				padding: 20px;
				background-color: #F8F9F9;
			}
			table {
				border-collapse: collapse;
				width: 500px;
			}
			td, th {
				padding: 10px;
			}
			th {
				background-color: #54585d;
				color: #ffffff;
				font-weight: bold;
				font-size: 13px;
				border: 1px solid #54585d;
			}
			td {
				color: #636363;
				border: 1px solid #dddfe1;
			}
			tr {
				background-color: #f9fafb;
			}
			tr:nth-child(odd) {
				background-color: #ffffff;
			}
			.pagination {
				list-style-type: none;
				padding: 10px 0;
				display: inline-flex;
				justify-content: space-between;
				box-sizing: border-box;
			}
			.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;
			}
			</style>
		</head>
		<body>
			<table>
				<tr>
					<th>Name</th>
					<th>Age</th>
					<th>Join Date</th>
				</tr>
				<?php while ($row = $result->fetch_assoc()): ?>
				<tr>
					<td><?php echo $row['name']; ?></td>
					<td><?php echo $row['age']; ?></td>
					<td><?php echo $row['joined']; ?></td>
				</tr>
				<?php endwhile; ?>
			</table>
			<?php if (ceil($total_pages / $num_results_on_page) > 0): ?>
			<ul class="pagination">
				<?php if ($page > 1): ?>
				<li class="prev"><a href="pagination.php?page=<?php echo $page-1 ?>">Prev</a></li>
				<?php endif; ?>

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

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

				<li class="currentpage"><a href="pagination.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="pagination.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="pagination.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="pagination.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="pagination.php?page=<?php echo $page+1 ?>">Next</a></li>
				<?php endif; ?>
			</ul>
			<?php endif; ?>
		</body>
	</html>
	<?php
	$stmt->close();
}
?>

Conclusion

Congratulations, you have successfully created a fully working pagination for your website, there are many different ways you can create pagination, this way is probably one of the best and it's secure! Remember to subscribe and share, like, follow our social media accounts if you like this tutorial, help our site grow and we will provide lots of more useful tutorials.