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.

Bootstrap pagination in PHP and MySQL

Database Table

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `link` varchar(255) NOT NULL,
  `content` longtext NOT NULL
)

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)

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','USERNAME');
define('DB_PASSWORD','PASSWORD');
define('DB_NAME', 'DATABASE-NAME');

//global $con;
$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
// Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

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

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

<div class="container-fluid mtb-margin-top">
  <div class="row">
    <div class="col-md-12">
      <?php
      $limit = 3;
      /*How may adjacent page links should be shown on each side of the current page link.*/
      $adjacents = 2;
      $sql = "SELECT COUNT(*) 'total_rows' FROM `posts`";
      $res = mysqli_fetch_object(mysqli_query($con, $sql));
      $total_rows = $res->total_rows;
      $total_pages = ceil($total_rows / $limit);
      
      
      if(isset($_GET['page']) && $_GET['page'] != "") {
        $page = $_GET['page'];
        $offset = $limit * ($page-1);
      } else {
        $page = 1;
        $offset = 0;
      }


      $query  = "select * from `posts` limit $offset, $limit";
      $result = mysqli_query($con, $query);
      if(mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_object($result)) {
          echo '<h1 class="post-title"><a href="'.$row->link.'" target="_blank">'.$row->title.'</a></h1>';
          echo '<p>'.$row->content.'</p>';
        }
      }

      //Here we generates the range of the page numbers which will display.
      if($total_pages <= (1+($adjacents * 2))) {
        $start = 1;
        $end   = $total_pages;
      } else {
        if(($page - $adjacents) > 1) { 
          if(($page + $adjacents) < $total_pages) { 
            $start = ($page - $adjacents);            
            $end   = ($page + $adjacents);         
          } else {					   
            $start = ($total_pages - (1+($adjacents*2)));  
            $end   = $total_pages;						   
          }
        } else {						   
          $start = 1;                                
          $end   = (1+($adjacents * 2));             
        }
      }

      //If you want to display all page links in the pagination then
      //uncomment the following two lines
      //and comment out the whole if condition just above it.
      /*$start = 1;
      $end = $total_pages;*/
      ?>

$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.

if($total_pages <= (1+($adjacents * 2))) {
   ..............
   ..............
}
//If you want to display all page links in the pagination then
//uncomment the following two lines
//and comment out the whole if condition just above it.
/*$start = 1;
$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)

<?php if($total_pages > 1) { ?>
          <ul class="pagination pagination-sm justify-content-center">
            <!-- Link of the first page -->
            <li class='page-item <?php ($page <= 1 ? print 'disabled' : '')?>'>
              <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=1'>&lt;&lt;</a>
            </li>
            <!-- Link of the previous page -->
            <li class='page-item <?php ($page <= 1 ? print 'disabled' : '')?>'>
              <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php ($page>1 ? print($page-1) : print 1)?>'>&lt;</a>
            </li>
            <!-- Links of the pages with page number -->
            <?php for($i=$start; $i<=$end; $i++) { ?>
            <li class='page-item <?php ($i == $page ? print 'active' : '')?>'>
              <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php echo $i;?>'><?php echo $i;?></a>
            </li>
            <?php } ?>
            <!-- Link of the next page -->
            <li class='page-item <?php ($page >= $total_pages ? print 'disabled' : '')?>'>
              <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php ($page < $total_pages ? print($page+1) : print $total_pages)?>'>&gt;</a>
            </li>
            <!-- Link of the last page -->
            <li class='page-item <?php ($page >= $total_pages ? print 'disabled' : '')?>'>
              <a class='page-link' href='MTB-php-pagination-with-bootstrap/?page=<?php echo $total_pages;?>'>&gt;&gt;                      
              </a>
            </li>
          </ul>
       <?php } ?>
       <?php mysqli_close($con); ?>
    </div>
 </div>
</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.

About Mitrajit

14 comments on “Bootstrap pagination in PHP and MySQL

  1. Hi I really found your tutorial very helpful and good. I also recently wrote a tutorial about it, i hope you will also find it helpful, i will be glad if you also share your reviews.

  2. why do I get this error in my innumerable number of characters:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1.5795795795796E+144, 2’ at line 127

  3. Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it?

  4. Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it??

  5. Hi,
    Thanks a lot for the tutorial. I had a small issue though. When i move to the next page, it erases all the CSS styling present. Going back to the first page makes no difference until i remove the
    ‘?page=1’ section from the URL? Any advice?
    Thanks

    1. Hi
      Nevermind my previous comment. I fixed the problem.

      It was caused by the ‘/’ before the ‘?page=’ in the pagination href. The / causes an error in the css filepath. Removing it fixes the problem.

      Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove you are a human *