Pagination in PHP & MySQL

To learn how to implement pagination in PHP and MySQL then you can follow the video link below or download the source code from Github or follow the steps given below.

Video Link

https://youtu.be/1IK_SSm4NKE

Step 1: Create a file with boilerplate and table

Here I have used bootstrap to take care of the styling for us.

<!-- index.php -->

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Pagination demo</title>
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-gH2yIJqKdNHPEq0n4Mqa/HGKIhSkIHeL5AyhkYV8i59U5AR6csBvApHHNl/vI1Bx" crossorigin="anonymous">
</head>
<body class="bg-dark text-light">


  <div class="container">
    <div class="row">
      <div class="col-md-8 mx-auto mt-5">

        <h3 class="mb-4">Pagination in PHP & MySQL</h3>

        <table class="table table-light table-striped">
          <thead>
            <tr>
              <th>Sr No.</th>
              <th>VALUES</th>
            </tr>
          </thead>
          <tbody>

          </tbody>
        </table>

        
      </div>
    </div>
  </div>


  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-A3rJD856KowSb7dwlZdYEkO39Gagi7vIsF0jrRAoQmDKKtQBHUuLZ9AsSv4jD4Xa" crossorigin="anonymous"></script>
</body>
</html>

Step 2: Fetch the whole data from the database

You need to fetch the whole data from the database and display it in the table body i.e. tbody tag.

So you need to connect to your database and write a query to fetch data from database and display it in the table body.

<table class="table table-light table-striped">
  <thead>
    <tr>
      <th>Sr No.</th>
      <th>VALUES</th>
    </tr>
  </thead>
  <tbody>
    <?php
      // connect to database
      $con = mysqli_connect("localhost","root","","");

      $query_all = "SELECT * FROM `data`";
      $result_all = mysqli_query($con,$query_all);

      $total_records = mysqli_num_rows($result_all);

      if($total_records == 0){
        echo"<tr><td colspan='2'>No records found!</td></tr>";
        exit;
      }
      
      while($data = mysqli_fetch_assoc($result_all))
      {
         echo"<tr>
           <th>$i</th>
           <td>$data[name]</td>
         </tr>";
         $i++;
      }
    ?>
  </tbody>
</table>

Step 3: Its time to implement pagination

Actually, we do not need to echo all data at once but we want to divide our data into a number of pages so that one page will show a specific number of data (Example 5 or 10 rows in one page). That is what actual pagination is!

.....

if($total_records == 0) {
  echo"<tr><td colspan='2'>No records found!</td></tr>";
  exit;
}

$limit = 5;  // Set limit of data that you want to show in one page
$page = 1;  // By default you will be on page one thats why $page=1

// Check if a get request is made for another page no. then toggle the $page value

if(isset($_GET['page'])) {
  $page = $_GET['page'];
}


// Limit data with offset
// $start defines how many records will be skipped

$start = ($page-1) * $limit;

/*
Example:

For $page=1 i.e. page 1 -> $start = (1-1)*5 => 0;
Means 0 records need to be skipped and 5 records will be shown

For $page=2 i.e. page 2 -> $start = (2-1)*5 => 5;
Means 5 records need to be skipped and next 5 records will be shown

For $page=3 i.e. page 3 -> $start = (3-1)*5 => 10;
Means 10 records need to be skipped and next 5 records will be shown

So on and so forth!
*/


// Write another query to show limited data and here $start will be used as offset.

$query_limit = "SELECT * FROM `data` LIMIT $start,$limit";
$result_limit = mysqli_query($con,$query_limit);


// Now use $start+1 for proper number in table data.

$i=$start+1;


// Now you need to use $result_limit to fetch limited data accordingly.

while($data = mysqli_fetch_assoc($result_limit))
{
  echo"<tr>
    <th>$i</th>
    <td>$data[name]</td>
  </tr>";
  $i++;
}

........

All done! Hope it would be helpful to you.

Github Link

https://github.com/tj-webdev/pagination-in-php-mysql

Share

Related Posts