For this tutorial, we will be creating pagination with PHP and MySQL. Pagination is how users will navigate certain parts of your website that consist of large amounts of data. We will be utilizing MySQL in this tutorial, which will select records from a database and populate them in a table using HTML and PHP while paginating data that exceeds a specific threshold. We will only display X number of records on each pagination page.
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.
It can be a tad tedious to correctly display the page elements, especially when dealing with the next and previous page numbers. Therefore, we'll implement logic to tackle this.
Pagination.php
Let's go ahead and create the pagination file — create a new file called pagination.php and place it on your development web server.
Connecting to the database is essential as it will be required to retrieve the records based on a set of params we'll define later, so for now, add the following code:
$mysqli = mysqli_connect('localhost', 'root', '', 'pagination');Update the connection details to reflect your MySQL database name and credentials. It should be in the following format:
mysqli_connect(YOUR_HOST, YOUR_USERNAME, YOUR_PASSWORD, YOUR_DATABASE)For the purpose of this tutorial, I have created the database pagination along with dummy data that we can leverage for testing purposes. Execute the SQL statement with phpMyAdmin, or your preferred database management tool.
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(1) NOT NULL,
`joined` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
INSERT INTO `students` (`id`, `name`, `age`, `joined`) VALUES
(1, 'David Deacon', 23, '17/08/2023'),
(2, 'Sheri Warner', 19, '03/05/2023'),
(3, 'Sean Glover', 24, '24/07/2022'),
(4, 'John West', 17, '13/08/2023'),
(5, 'Rufus Clarke', 20, '28/07/2023'),
(6, 'Roosevelt Myers', 20, '25/07/2023'),
(7, 'Elvira Andrews', 22, '02/07/2023'),
(8, 'Richard Cook', 26, '19/07/2022'),
(9, 'Lorenzo Harris', 23, '01/07/2022'),
(10, 'Eduardo Hoffman', 17, '03/07/2023'),
(11, 'Jeanne Fisher', 20, '13/08/2023'),
(12, 'Tracy Bowers', 30, '07/07/2022'),
(13, 'Heidi Lawrence', 18, '04/06/2023'),
(14, 'Tara Holland', 25, '01/07/2023'),
(15, 'Grant Edwards', 22, '22/06/2022'),
(16, 'Bradford Green', 29, '02/05/2023'),
(17, 'Gwen Schultz', 20, '02/05/2023'),
(18, 'Hope Dawson', 28, '21/08/2023'),
(19, 'Florence Osborne', 19, '17/05/2023'),
(20, 'Rickey Poole', 26, '28/06/2023'),
(21, 'Casey Sutton', 28, '06/07/2023'),
(22, 'Willie Lowe', 23, '11/05/2023'),
(23, 'Stephen Schultz', 28, '15/07/2022'),
(24, 'Eileen Lynch', 18, '12/06/2022'),
(25, 'Aaron Ruiz', 29, '02/05/2023'),
(26, 'Mae Murray', 30, '24/06/2023'),
(27, 'Regina Hanson', 21, '26/07/2023'),
(28, 'Cameron Mclaughlin', 20, '29/07/2023'),
(29, 'Earl Hale', 17, '30/06/2023'),
(30, 'Marta Blair', 24, '10/06/2023'),
(31, 'Alberta Silva', 22, '05/06/2022'),
(32, 'Joanna Holmes', 20, '20/05/2023'),
(33, 'Alex Brock', 30, '12/05/2023'),
(34, 'Colin Wright', 19, '28/05/2022'),
(35, 'Peter Schmidt', 25, '10/07/2023'),
(36, 'Joshua Price', 27, '13/07/2023'),
(37, 'Elias Chandler', 22, '19/07/2023'),
(38, 'Stanley Ross', 21, '02/06/2023'),
(39, 'Vera Cole', 26, '02/05/2023'),
(40, 'Johnny Daniels', 29, '19/07/2023'),
(41, 'Yvonne Hopkins', 21, '16/07/2023');
ALTER TABLE `students` ADD PRIMARY KEY (`id`);
ALTER TABLE `students` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;We can leverage the students' table to populate records in our HTML table later on in the tutorial.
Let's go back to the pagination PHP file and declare essential variables that we can use for our pagination.
// Get the total number of records from our table "students".
$total_pages = $mysqli->query('SELECT COUNT(*) FROM students')->fetch_row()[0];
// 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 retrieving the total number of rows from the students table in our database, as this will be used to calculate how many pages there will be. The second variable is used to determine which page the user is currently on — for example, if the URI is pagination.php?page=3, then we can assume the page number will be 3, but if the page parameter doesn't exist in the URL, it will default to 1.
Did You Know? The isset() function will check if the specified variable exists and therefore will help prevent runtime errors with the script.
Finally, the third variable determines how many results will be shown on each page — for example, if we wanted to show 10 results (10 student records from our database table), we will change the number to 10 (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 we need to consider in the above code — initially, we are preparing the SQL statement, which prevents SQL injection and therefore secures our SQL query. If we do not secure our SQL statements, our script is vulnerable to attacks. 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.
Next up — we need to display these results, so to do that, we can utilize the while loop and iterate the results accordingly. You can display the results how you want, but for this tutorial, we will use an HTML 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 iterate the SQL results and display each record in the table with the name, age, and joined date columns.
Finally, we can implement the pagination code in our script.
<?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 is 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 onwards, but in reverse.
Let's implement code to structure our layout and make it look more appealing. Add the below code to your CSS stylesheet or <head> section of the document.
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 resembles the following:
Source Code
The full pagination.php source code is below.
<?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();
}
?>If you're looking for a more advanced solution, consider the advanced CRUD application at the end of this post. It includes a lot more unique features, enhanced security, and will help us write more tutorials like this.
Conclusion
Congratulations! You have successfully created a fully working pagination for your website. There are many different approaches you can take to incorporate pagination into your project, but this way is probably one of the best approaches, and it's secure!
Remember to subscribe and share, like, follow our social media accounts if you like this tutorial, as it will help our site grow, and we shall provide you with lots of more useful tutorials and free code.
Enjoy coding!
If you would like to support us, consider the advanced crud application below. It will greatly help us create more tutorials and keep our website up and running. The advanced package includes improved code and more features.
Basic
Advanced
* Advanced package also includes the tutorial source and basic package.
* Instant download after payment.
For more detailed information regarding the advanced package, click here.