Ajax Pagination using jQuery with PHP and MySQL

Pagination is very useful for data lists, which include a large number of records in a database. This helps load dynamic data faster by embedding notes across multiple pages. The page functionality can easily be integrated with PHP. PHP Pagination reloads data by navigating the link by reloading the page. If you want to improve the user interface of your website and create easy-to-use data lists, Ajax Pagination is your best choice. you can also use Load more pagination using jquery AJAX with PHP and Mysql

Ajax pagination allows you to create links for pages and load data without refreshing the page. You can add pages to the data list without reloading the page using Ajax and PHP. In this Post we will show you how to integrate a page into a website using jQuery, Ajax, PHP and MySQL.

In the example script, we will retrieve dynamic data from a MySQL database and list it with a user details. This page link is used to retrieve a number of records from the database without having to reload the page using jQuery and Ajax. Ajax is used to implement functions for one-sided pages without reloading the page.

Table of Contents

Create Database Table

--
-- Database: `registration`
-- --------------------------------------------------------
-- Table structure for table `users`

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `age` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Database Connectivity

dbConfig.php

<?php

	// database configuration 

	$sernamename = "localhost";
	$username    = "root";
	$passoword   = "";
	$databasename= "registration";

	// create database connection
	$con = mysqli_connect($sernamename, $username,$passoword,$databasename);

	// check connection
	if ($con->connect_error) {
		die("Connection failed". $con->connect_error);
	}

?>

Create HTML Page Show Data list from Database

index.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Ajax Pagination using jQuery with PHP and MySQL</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>

<div class="jumbotron text-center">
  <h1>Ajax Pagination using jQuery with PHP and MySQL</h1>
</div>
  
<div class="container">
  <div class="row">
    <div class="col-md-12">
      <div id="table-data">
        
      </div>
    </div>
  </div>
</div>

</body>
</html>

jQuery AJAX Code for fetch Data and Pagination

<script type="text/javascript">
  $(document).ready(function(){
    function loadData(page){
      $.ajax({
        url  : "pagination.php",
        type : "POST",
        cache: false,
        data : {page_no:page},
        success:function(response){
          $("#table-data").html(response);
        }
      });
    }
    loadData();
    
    // Pagination code
    $(document).on("click", ".pagination li a", function(e){
      e.preventDefault();
      var pageId = $(this).attr("id");
      loadData(pageId);
    });
  });
</script>

Create PHP page for fetch Data list and Pagination

pagination.php

<?php

	// Connect database 

	require_once('dbConfig.php');

	$limit = 5;

	if (isset($_POST['page_no'])) {
	    $page_no = $_POST['page_no'];
	}else{
	    $page_no = 1;
	}

	$offset = ($page_no-1) * $limit;

	$query = "SELECT * FROM users LIMIT $offset, $limit";

	$result = mysqli_query($con, $query);

	$output = "";

	if (mysqli_num_rows($result) > 0) {

	$output.="<table class='table'>
		    <thead>
		        <tr>
		           <th>Id</th>
    	          	   <th>Name</th>
			   <th>Username</th>
			   <th>Email</th>
			   <th>Age</th>
	                 </tr>
		    </thead>
	         <tbody>";
	while ($row = mysqli_fetch_assoc($result)) {

	$output.="<tr>
	            <td>{$row['id']}</td>
	            <td>{$row['name']}</td>
	            <td>{$row['username']}</td>
	            <td>{$row['email']}</td>
	            <td>{$row['age']}</td>
		 </tr>";
	} 
	$output.="</tbody>
		</table>";

	$sql = "SELECT * FROM users";

	$records = mysqli_query($con, $sql);

	$totalRecords = mysqli_num_rows($records);

	$totalPage = ceil($totalRecords/$limit);

	$output.="<ul class='pagination justify-content-center' style='margin:20px 0'>";

	for ($i=1; $i <= $totalPage ; $i++) { 
	   if ($i == $page_no) {
		$active = "active";
	   }else{
		$active = "";
	   }

	    $output.="<li class='page-item $active'><a class='page-link' id='$i' href=''>$i</a></li>";
	}

	$output .= "</ul>";

	echo $output;

	}

?>

Output

You can always support by sharing on social media or recommending my blog to your friends and colleagues.

If you have any suggestions / problems about this tutorial, please comment on the form below.😊