Bootstrap pagination in PHP and MySQL

It is possible to have thousands of records on your table. But displaying all the records on a single page is not a good idea. So you can split or divide these records into multiple pages and you have to provide each page link at the bottom of the contents. We will achieve this by using pagination mechanism. Pagination in PHP and MySQL is very simple. Another thing, we will use here, that is, Bootstrap pagination. By using simple bootstrap classes easily you can achieve bootstrap pagination in PHP. So, in this tutorial, I will show you how to do Bootstrap pagination in PHP and MySQL. You may also like to read — Load data dynamically on page scrolling using jQuery, Ajax, PHP, and MySQL.

Database Table

1. CREATE TABLE `posts` (
2. `id` int(11) NOT NULL,
3. `title` varchar(255) NOT NULL,
4. `link` varchar(255) NOT NULL,
5. `content` longtext NOT NULL
6. )

Run the above query and create the posts table in your database. Download the complete source code from the Download link, where you will get the sample data for the posts table.

Database Configuration (db.php)

1. <?php
2. define('HOSTNAME','localhost');
3. define('DB_USERNAME','USERNAME');
4. define('DB_PASSWORD','PASSWORD');
5. define('DB_NAME', 'DATABASE-NAME');
7. //global $con;
8. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
9. // Check connection
10. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
11. ?>

Change USERNAME, PASSWORD, and DATABASE-NAME with your database credentials.

Logic – Generate Pagination in PHP and MySQL (index.php)

1. <div class="container-fluid mtb-margin-top">
2. <div class="row">
3. <div class="col-md-12">
4. <?php
5. $limit = 3;
6. /*How may adjacent page links should be shown on each side of the current page link.*/
7. $adjacents = 2;
8. $sql = "SELECT COUNT(*) 'total_rows' FROM `posts`";
9. $res = mysqli_fetch_object(mysqli_query($con, $sql));
10. $total_rows = $res->total_rows;
11. $total_pages = ceil($total_rows / $limit);
14. if(isset($_GET['page']) && $_GET['page'] != "") {
15. $page = $_GET['page'];
16. $offset = $limit * ($page-1);
17. } else {
18. $page = 1;
19. $offset = 0;
20. }
23. $query = "select * from `posts` limit $offset, $limit";
24. $result = mysqli_query($con, $query);
25. if(mysqli_num_rows($result) > 0) {
26. while($row = mysqli_fetch_object($result)) {
27. echo '<h1 class="post-title"><a href="'.$row->link.'" target="_blank">'.$row->title.'</a></h1>';
28. echo '<p>'.$row->content.'</p>';
29. }
30. }
32. //Here we generates the range of the page numbers which will display.
33. if($total_pages <= (1+($adjacents * 2))) {
34. $start = 1;
35. $end = $total_pages;
36. } else {
37. if(($page - $adjacents) > 1) {
38. if(($page + $adjacents) < $total_pages) {
39. $start = ($page - $adjacents);
40. $end = ($page + $adjacents);
41. } else {
42. $start = ($total_pages - (1+($adjacents*2)));
43. $end = $total_pages;
44. }
45. } else {
46. $start = 1;
47. $end = (1+($adjacents * 2));
48. }
49. }
51. //If you want to display all page links in the pagination then
52. //uncomment the following two lines
53. //and comment out the whole if condition just above it.
54. /*$start = 1;
55. $end = $total_pages;*/
56. ?>

$limit = 3 – Limits the number of records in each page.

$adjacents = 2 –  How may adjacent page links be shown on each side of the current page link. You can change it as per your need.

1. if($total_pages <= (1+($adjacents * 2))) {
2. ..............
3. ..............
4. }
5. //If you want to display all page links in the pagination then
6. //uncomment the following two lines
7. //and comment out the whole if condition just above it.
8. /*$start = 1;
9. $end = $total_pages;*/

Maybe the total number of pages are too many so that all page links will not be accommodated within a single line. To eliminate such kind of situation we will display a limited number of page links in the pagination. In this bootstrap pagination tutorial, we will display the current page link and two adjacent page links each side of the current page link. The adjacent links will change automatically based on the current page number.

If you want to display all page links in the pagination then simply remove or comment out the above if condition and all codes within it and uncomment the two lines just below it.

HTML – Bootstrap Pagination (index.php)

1. <?php if($total_pages > 1) { ?>
2. <ul class="pagination pagination-sm justify-content-center">
3. <!-- Link of the first page -->
4. <li class='page-item <?php ($page <= 1 ? print 'disabled' : '')?>'>
5. <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=1'><<</a>
6. </li>
7. <!-- Link of the previous page -->
8. <li class='page-item <?php ($page <= 1 ? print 'disabled' : '')?>'>
9. <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php ($page>1 ? print($page-1) : print 1)?>'><</a>
10. </li>
11. <!-- Links of the pages with page number -->
12. <?php for($i=$start; $i<=$end; $i++) { ?>
13. <li class='page-item <?php ($i == $page ? print 'active' : '')?>'>
14. <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php echo $i;?>'><?php echo $i;?></a>
15. </li>
16. <?php } ?>
17. <!-- Link of the next page -->
18. <li class='page-item <?php ($page >= $total_pages ? print 'disabled' : '')?>'>
19. <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php ($page < $total_pages ? print($page+1) : print $total_pages)?>'>></a>
20. </li>
21. <!-- Link of the last page -->
22. <li class='page-item <?php ($page >= $total_pages ? print 'disabled' : '')?>'>
23. <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php echo $total_pages;?>'>>>
24. </a>
25. </li>
26. </ul>
27. <?php } ?>
28. <?php mysqli_close($con); ?>
29. </div>
30. </div>
31. </div>

In Bootstrap 3 the only class you required that is .pagination to be added to the <ul> element. But in Bootstrap 4 along with .pagination, you also required two other classes. The first class is .page-item class to be added to each <li>. And the second clas is .page-link class to be added to each <a> element within the <li>.

Bootstrap Pagination – Large and Small Size

In Bootstrap 4 you can resize pagination by adding .pagination-sm or .pagination-lg class in the <ul> element.

Right or Center Alignment of Pagination

Bootstrap 4 allows you to align pagination according to your choice. For right alignment use .justify-content-end and for center alignment use .justify-content-center class in the <ul>element.

Active and Disabled Link in Pagination

By using .active class you can show a link as activated. For disable any link use .disabled class.

Complete Source Code – Bootstrap Pagination in PHP and MySQL

Click on the Demo link to try the live demo of Bootstrap pagination in PHP and MySQL. Also, you can download the complete source code from the Download link. Please link and share this tutorial with others.