If you looking for tutorial on Ajax Live Data Search with Pagination in PHP Mysql using Ajax, then you have land on right page. Because in this post, we have make tutorial on Instant Mysql Data search with Pagination feature by using PHP script with Ajax and jQuery. If you are using PHP for you web development, then you have know Data Search and Pagination, both are required functionality of any web based application. So, you have to learn both things, because Search and Pagination both are used in most of the PHP based web application.
In Web development, Search is the most powerful functionality in the Mysql Database management section. And if Search functionality is live or instant, then you can quickly ge the filter data from the large amount of data. If you are User then you can get the relevant set of data from the large list of records in a seconds. For make live search feature, here we have use Ajax with PHP script. So, we can get the filter data on web page without refresh of web page. So, by using Ajax with PHP script we can easily implement Live search functionality for our PHP based web application.
If we have use Live search feature in our web application, then there is large amount of data has been filter from Mysql database, then on single web page we have load large filter data, then it will slow down our application. For prevent loading of large filter data on single web page, here we have use the Pagination functionality of web development. Here we will implement pagination without page refresh, because here also for Pagination we will use Ajax with PHP script. So we can easily implement Ajax pagination with PHP script. If we have use Ajax Pagination, then it will make the list of data more user friendly. In this post, we will implement Ajax Live Search with Pagination feature in PHP application. Below you can find the complement source of code this tutorial.
Source Code
index.php
<!DOCTYPE html>
<html>
<head>
<title>Live Data Search with Pagination in PHP using Ajax</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.3/css/bootstrap.min.css" integrity="sha384-Zug+QiDoJOrZ5t4lssLdxGhVrurbmBWopoEl+M6BdEfwnCJZtKxi1KgxUyJq13dy" crossorigin="anonymous">
<link rel="stylesheet" href="https://unpkg.com/placeholder-loading/dist/css/placeholder-loading.min.css">
</head>
<body>
<br />
<div class="container">
<h3 align="center">Live Data Search with Pagination in PHP Mysql using Ajax</h3>
<br />
<div class="card">
<div class="card-header">Dynamic Data</div>
<div class="card-body">
<div class="form-group">
<input type="text" name="search_box" id="search_box" class="form-control" placeholder="Type your search query here" />
</div>
<div class="table-responsive" id="dynamic_content">
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
load_data(1);
function load_data(page, query = '')
{
$.ajax({
url:"fetch.php",
method:"POST",
data:{page:page, query:query},
success:function(data)
{
$('#dynamic_content').html(data);
}
});
}
$(document).on('click', '.page-link', function(){
var page = $(this).data('page_number');
var query = $('#search_box').val();
load_data(page, query);
});
$('#search_box').keyup(function(){
var query = $('#search_box').val();
load_data(1, query);
});
});
</script>
fetch.php
<?php
$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");
/*function get_total_row($connect)
{
$query = "
SELECT * FROM tbl_webslesson_post
";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
$total_record = get_total_row($connect);*/
$limit = '5';
$page = 1;
if($_POST['page'] > 1)
{
$start = (($_POST['page'] - 1) * $limit);
$page = $_POST['page'];
}
else
{
$start = 0;
}
$query = "
SELECT * FROM tbl_webslesson_post
";
if($_POST['query'] != '')
{
$query .= '
WHERE webslesson_post_title LIKE "%'.str_replace(' ', '%', $_POST['query']).'%"
';
}
$query .= 'ORDER BY webslesson_post_id ASC ';
$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();
$total_filter_data = $statement->rowCount();
$output = '
<label>Total Records - '.$total_data.'</label>
<table class="table table-striped table-bordered">
<tr>
<th>ID</th>
<th>Post Title</th>
</tr>
';
if($total_data > 0)
{
foreach($result as $row)
{
$output .= '
<tr>
<td>'.$row["webslesson_post_id"].'</td>
<td>'.$row["webslesson_post_title"].'</td>
</tr>
';
}
}
else
{
$output .= '
<tr>
<td colspan="2" align="center">No Data Found</td>
</tr>
';
}
$output .= '
</table>
<br />
<div align="center">
<ul class="pagination">
';
$total_links = ceil($total_data/$limit);
$previous_link = '';
$next_link = '';
$page_link = '';
//echo $total_links;
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:void(0)" data-page_number="'.$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:void(0)" data-page_number="'.$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:void(0)" data-page_number="'.$page_array[$count].'">'.$page_array[$count].'</a></li>
';
}
}
}
$output .= $previous_link . $page_link . $next_link;
$output .= '
</ul>
</div>
';
echo $output;
?>
By follow Source code of Ajax Live Mysql Data Search with Pagination using PHP script, then you can definately build or PHP Ajax Live Search box for you web application and if you still want to get any assistance, then you can email us at webslesson@gmail.com.
Hi @Webslesson amazing job again :) can share how can I use this script but with multiple fields?
ReplyDeleteMy ideia, searh for type = grass and color = red|blue|green and client = armando
And list grass green armando
display error when data cannot be found
ReplyDeleteNotice: Undefined variable: page_array in C:\xampp\htdocs\castright\admin\controller\contol.search.all.php on line 103
Warning: count(): Parameter must be an array or an object that implements Countable in C:\xampp\htdocs\castright\admin\controller\contol.search.all.php on line 103
I have solve this error' easily
DeleteNotice: Undefined variable: page_array in C:\wamp64\www\cour\fetch.php on line 140
ReplyDeletehelp
you should change line:
ReplyDeleteif($next_id >= $total_links)
to
if($next_id > $total_links)
or it will screw up the pagination.
You might also want to add a clause before the line:
for($count = 0; $count < count($page_array); $count++)
Before the line add the line:
if(!$total_data == 0) {
and then close the curly brace after the
for($count = 0; $count < count($page_array); $count++) section, this will prevent errors from appearing if nothing in the db is found. There is one more problem with your script :) if you click on a page number in the pagination and click the same number again it will produce an error: Notice: Undefined index: page in ..., I will be looking at bug fixing this later today. nice script though.
Saved the day, tks!
DeleteHello DHubAd, have you find solution for error when user click same page number?
DeleteDid you find any solution for that annoying bug :D ?
DeleteHi All
DeleteDid all your fixes and they work :D Great stuff...
I'm a major PHP newbie but did some trial and error on the outstanding bug fix from DHubAd regarding error when you click the same page number.
Try this and if it is a success, then could someone more clever than me expain why it actually works "if($page_array[$count] < 0 )" ... :D :D :D
PS: It wouldn't accept the full code change in this reply due to HTML restrictions. But hope it still makes sense.
Before:
$page_link .= ' li class="page-item active"
Add:
if($page_array[$count] < 0 )
And then in your else statement change the "active" part to "disabled":
li class="page-item disabled"
in pagination fetch.php:
ReplyDeleteif($next_id > $total_links)
change > instead of >=
display error when data cannot be found
ReplyDeleteNotice: Undefined variable: page_array in C:\xampp\htdocs\castright\admin\controller\contol.search.all.php on line 103
Warning: count(): Parameter must be an array or an object that implements Countable in C:\xampp\htdocs\castright\admin\controller\contol.search.all.php on line 103
did you ever fix this problem?
Have you found the solution?
DeleteI solved it by add a line $page_array[] = "$count"; before the line for($count = 0; $count < count($page_array); $count++)
DeleteAnd how to get url parametr?
ReplyDeleteHi, I have used this for ym image gallery . But the pagination numbers keeps jumping around and also does not stay at the bottom of the Gallery list. any solution for this.?
ReplyDeleteI have used this for the purpose of images gallery. It is working fine but he pagination is jumping around when you click next page and when the images in the fetch.php is $limit = ‘4’; is when increased to $limit = ‘12’;. then pagination jumps to right of the gallery . Any help in this regards would be nice.. Can you please let me know how to position the pagination.
ReplyDeletebecause you removed the table tag from the output variable in the beginning.
Deletethanks for your describing the code.
ReplyDeleteplease insert this code with your product filter tutorial. (https://www.webslesson.info/2018/08/how-to-make-product-filter-in-php-using-ajax.html). Really need this all code combined. I tried but nothing is working. Please create and send me on my email id: website.vrts@gmail.com. Please help me sir.
ReplyDeletePlease help me sir with your code. I need this code with product filter code. Link is(https://www.webslesson.info/2018/08/how-to-make-product-filter-in-php-using-ajax.html). Requied search and pagination with product filter code. I tried to add the upper code on that. But bad luck I was getting full errors. Please help sir. If possible send code on my email id: website.vrts@gmail.com
ReplyDeletePagination is very nice thank you
ReplyDeleteHow can I use it with Mysqli
ok good job
ReplyDeleteadd $page_array=array();
ReplyDeleteExito...
DeleteFound a mistake to find: if ($next_id >= $total_links) {
ReplyDeleteReplaced by: if ($next_id > $total_links) {
1) Found a mistake, find: if ($next_id >= $total_links) {
ReplyDeleteReplaced by: if ($next_id > $total_links) {
2) When 41 records of a digit double: https://i.ibb.co/k0RYjpy/1.jpg
how to pass get url id in pagination
ReplyDeleteThanks the code. i would like to have search fields for each column just below the column title in the above code. please suggest the script
ReplyDeletei am suffering from an error in search. when I am searching that show
ReplyDeleteNotice: Undefined variable: page_array in C:\laragon\www\daraz\fetch.php on line 154
Warning: count(): Parameter must be an array or an object that implements Countable in C:\laragon\www\daraz\fetch.php on line 154
Please give a solution to resolve the problem
Thanks for this project. it helped me alot.
ReplyDeleteHow to convert pdo to myqli?
ReplyDeleteThank for wonderful work, please can i have the source code for this tutorial.
ReplyDeletewhen you click the active page number you'll get this "Warning: Undefined array key "page"". you can fix this by changing the "page-item active" to "page-item disabled" in: if($page == $page_array[$count])
ReplyDeleteBut the design will change too but you can't click the active page number to prevent error.
How to change the load_data(1) to load_data(page)? How could I edit in other pages like page 2 without having a refresh? or How can I dynamically change the load_data() page when I have to edit in page
ReplyDeletekumarrajesh1rs@gmail.com
ReplyDeletekindly send correct code
Notice: Undefined variable: page_array in C:\xampp\htdocs\jquery_pagination\fetch.php on line 140
Warning: count(): Parameter must be an array or an object that implements Countable in C:\xampp\htdocs\jquery_pagination\fetch.php on line 140
Total Records - 0
Hello! ... Can I run a database?
ReplyDeleteI have fix it when click 2 time on the same page number by edit:
ReplyDelete< a class="page-link" href="#">'.$page_array[$count].' < span class="sr-only">(current)< /span>< /a>"
TO:
< a class="page-link" href="#" disabled="disabled">'.$page_array[$count].' < span class="sr-only">(current)< /span>< /a>"
AND add this to my css:
a[disabled="disabled"] {
pointer-events: none;
}
Hello! How can I set it to search after entering 3 characters?
ReplyDeletei use my own solution..but it work..
ReplyDeletethe if($_POST['page'] > 1) i change to if(isset($_POST['Cert_action']))
then in javasciption side..for onclick i change to
$(document).on('click', '.page-link', function(){
var Cert_action = $(this).data('page_number');
var select = $('#searchCertArowana').val();
if (Cert_action >=1){
fetch_CertArowana_data(Cert_action, select);
}else{
e.preventDefault();
}
});
Please how can i let this support arabic search ,
ReplyDeletehow to make the ful search
ReplyDelete