Pagination in Web Application is one of the required feature for load data on web page, in which include load large number of data from database. The main benefit of Pagination is the load dynamic data faster because it has divided data loading process in multiple pages. This data loading functionality we can easily implement with the help of PHP script. In PHP Pagination which has load data by click on navigation links and when we have click on navigation link the it has reload page with dynamic data. Suppose you want to improve your web application user interface and if you want to make easy to use your web application data, then Ajax pagination is the best option because with the help of Ajax pagination it has load dynamic data without reloading whole page when we have click on pagination navigation link.
With the help of Ajax Pagination which will make a dynamic navigation links for pages and then after it has load data without reloading whole web page content. So by using Ajax Pagination we can add dynamic page content in the data list without refreshing the web page content by using Ajax and PHP. Under this tutorial you can find how to integrate Pagination into your website by using JavaScript with PHP script and MySQL database.
Advantages of Pagination
- Pagination has separate the whole data into different page, so it has reduce load on fetch whole data from database.
- By using Pagination we can easily go to any number of page data by directly click on navigation link.
- In pagination we can directly go to last records or first record by directly click on First page or Last page navigation link.
In this tutorial, we have make simple script in which we have fetch dynamic data from a MySQL database with Pagination dynamic navigation links with previous and next link. In this tutorial, we will use pure javaScript function which will send Ajax request to PHP script for fetch data from database and then after in PHP script we will also write PHP script for create pagination link with next and previous button link. So this page link will used for fetch a number of data from database without reload page using JavaScript and Ajax.
Create Database Table
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `post`
--
CREATE TABLE `post` (
`id` mediumint(8) UNSIGNED NOT NULL,
`post_title` text,
`post_description` text,
`author` varchar(255) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`post_image` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `post`
--
ALTER TABLE `post`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT;
Create HTML Page for Display Data from MySQL Database
<!--index.html-->
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8" />
<title>Ajax Pagination using JavaScript with PHP and MySQL</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
<div class="container">
<h2 class="text-center mt-4 mb-4">Ajax Pagination using JavaScript with PHP and MySQL</h2>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col-md-6">Sample Data</div>
<div class="col-md-3 text-right"><b>Total Data - <span id="total_data"></span></b></div>
<div class="col-md-3">
<input type="text" name="search" class="form-control" id="search" placeholder="Search Here" onkeyup="load_data(this.value);" />
</div>
</div>
</div>
<div class="card-body">
<table class="table table-bordered">
<thead>
<tr>
<th width="5%">#</th>
<th width="35%">Post Title</th>
<th width="60%">Description</th>
</tr>
</thead>
<tbody id="post_data"></tbody>
</table>
<div id="pagination_link"></div>
</div>
</div>
</div>
</body>
</html>
JavaScript & Ajax Code for Retrive Data and Pagination
function load_data(query = '', page_number = 1)
{
var form_data = new FormData();
form_data.append('query', query);
form_data.append('page', page_number);
var ajax_request = new XMLHttpRequest();
ajax_request.open('POST', 'process_data.php');
ajax_request.send(form_data);
ajax_request.onreadystatechange = function()
{
if(ajax_request.readyState == 4 && ajax_request.status == 200)
{
var response = JSON.parse(ajax_request.responseText);
var html = '';
var serial_no = 1;
if(response.data.length > 0)
{
for(var count = 0; count < response.data.length; count++)
{
html += '<tr>';
html += '<td>'+response.data[count].post_id+'</td>';
html += '<td>'+response.data[count].post_title+'</td>';
html += '<td>'+response.data[count].post_description+'</td>';
html += '</tr>';
serial_no++;
}
}
else
{
html += '<tr><td colspan="3" class="text-center">No Data Found</td></tr>';
}
document.getElementById('post_data').innerHTML = html;
document.getElementById('total_data').innerHTML = response.total_data;
document.getElementById('pagination_link').innerHTML = response.pagination;
}
}
}
PHP Script for Fetch Data & Pagination
<?php
//process_data.php
if(isset($_POST["query"]))
{
$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");
$data = array();
$limit = 5;
$page = 1;
if($_POST["page"] > 1)
{
$start = (($_POST["page"] - 1) * $limit);
$page = $_POST["page"];
}
else
{
$start = 0;
}
if($_POST["query"] != '')
{
$condition = preg_replace('/[^A-Za-z0-9\- ]/', '', $_POST["query"]);
$condition = trim($condition);
$condition = str_replace(" ", "%", $condition);
$sample_data = array(
':post_title' => '%' . $condition . '%',
':post_description' => '%' . $condition . '%'
);
$query = "
SELECT id, post_title, post_description
FROM post
WHERE post_title LIKE :post_title
OR post_description LIKE :post_description
ORDER BY id DESC
";
$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit . '';
$statement = $connect->prepare($query);
$statement->execute($sample_data);
$total_data = $statement->rowCount();
$statement = $connect->prepare($filter_query);
$statement->execute($sample_data);
$result = $statement->fetchAll();
$replace_array_1 = explode('%', $condition);
foreach($replace_array_1 as $row_data)
{
$replace_array_2[] = '<span style="background-color:#'.rand(100000, 999999).'; color:#fff">'.$row_data.'</span>';
}
foreach($result as $row)
{
$data[] = array(
'post_id' => $row["id"],
'post_title' => str_ireplace($replace_array_1, $replace_array_2, $row["post_title"]),
'post_description' => str_ireplace($replace_array_1, $replace_array_2, $row["post_description"])
);
}
}
else
{
$query = "
SELECT id, post_title, post_description
FROM post
ORDER BY id DESC
";
$filter_query = $query . ' LIMIT ' . $start . ', ' . $limit . '';
$statement = $connect->prepare($query);
$statement->execute();
$total_data = $statement->rowCount();
$statement = $connect->prepare($filter_query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$data[] = array(
'post_id' => $row["id"],
'post_title' => $row['post_title'],
'post_description' => $row['post_description']
);
}
}
$pagination_html = '
<div align="center">
<ul class="pagination">
';
$total_links = ceil($total_data/$limit);
$previous_link = '';
$next_link = '';
$page_link = '';
if($total_links > 4)
{
if($page < 5)
{
for($count = 1; $count <= 5; $count++)
{
$page_array[] = $count;
}
$page_array[] = '...';
$page_array[] = $total_links;
}
else
{
$end_limit = $total_links - 5;
if($page > $end_limit)
{
$page_array[] = 1;
$page_array[] = '...';
for($count = $end_limit; $count <= $total_links; $count++)
{
$page_array[] = $count;
}
}
else
{
$page_array[] = 1;
$page_array[] = '...';
for($count = $page - 1; $count <= $page + 1; $count++)
{
$page_array[] = $count;
}
$page_array[] = '...';
$page_array[] = $total_links;
}
}
}
else
{
for($count = 1; $count <= $total_links; $count++)
{
$page_array[] = $count;
}
}
for($count = 0; $count < count($page_array); $count++)
{
if($page == $page_array[$count])
{
$page_link .= '
<li class="page-item active">
<a class="page-link" href="#">'.$page_array[$count].' <span class="sr-only">(current)</span></a>
</li>
';
$previous_id = $page_array[$count] - 1;
if($previous_id > 0)
{
$previous_link = '<li class="page-item"><a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$previous_id.')">Previous</a></li>';
}
else
{
$previous_link = '
<li class="page-item disabled">
<a class="page-link" href="#">Previous</a>
</li>
';
}
$next_id = $page_array[$count] + 1;
if($next_id >= $total_links)
{
$next_link = '
<li class="page-item disabled">
<a class="page-link" href="#">Next</a>
</li>
';
}
else
{
$next_link = '
<li class="page-item"><a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$next_id.')">Next</a></li>
';
}
}
else
{
if($page_array[$count] == '...')
{
$page_link .= '
<li class="page-item disabled">
<a class="page-link" href="#">...</a>
</li>
';
}
else
{
$page_link .= '
<li class="page-item">
<a class="page-link" href="javascript:load_data(`'.$_POST["query"].'`, '.$page_array[$count].')">'.$page_array[$count].'</a>
</li>
';
}
}
}
$pagination_html .= $previous_link . $page_link . $next_link;
$pagination_html .= '
</ul>
</div>
';
$output = array(
'data' => $data,
'pagination' => $pagination_html,
'total_data' => $total_data
);
echo json_encode($output);
}
?>
Conclusion
So Lastly, In this post, We have learned how to make Ajax Pagination in PHP & MySQL by using Pure Vanilla JavaScript.
If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.
