Tuesday, 4 February 2020

Instant Search with Pagination in PHP Mysql jQuery and Ajax



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.




19 comments:

  1. Hi @Webslesson amazing job again :) can share how can I use this script but with multiple fields?
    My ideia, searh for type = grass and color = red|blue|green and client = armando
    And list grass green armando

    ReplyDelete
  2. display error when data cannot be found

    Notice: 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

    ReplyDelete
  3. Notice: Undefined variable: page_array in C:\wamp64\www\cour\fetch.php on line 140
    help

    ReplyDelete
  4. you should change line:

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

    ReplyDelete
  5. in pagination fetch.php:

    if($next_id > $total_links)

    change > instead of >=

    ReplyDelete
  6. display error when data cannot be found

    Notice: 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?

    ReplyDelete
  7. And how to get url parametr?

    ReplyDelete
  8. Hi, 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.?

    ReplyDelete
  9. I 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.

    ReplyDelete
  10. please 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.

    ReplyDelete
  11. Please 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

    ReplyDelete
  12. Pagination is very nice thank you
    How can I use it with Mysqli

    ReplyDelete
  13. Found a mistake to find: if ($next_id >= $total_links) {
    Replaced by: if ($next_id > $total_links) {

    ReplyDelete
  14. 1) Found a mistake, find: if ($next_id >= $total_links) {
    Replaced by: if ($next_id > $total_links) {

    2) When 41 records of a digit double: https://i.ibb.co/k0RYjpy/1.jpg

    ReplyDelete