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.




39 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
    Replies
    1. Hello DHubAd, have you find solution for error when user click same page number?

      Delete
    2. Did you find any solution for that annoying bug :D ?

      Delete
    3. Hi All

      Did 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"

      Delete
  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
    Replies
    1. Have you found the solution?

      Delete
    2. I solved it by add a line $page_array[] = "$count"; before the line for($count = 0; $count < count($page_array); $count++)

      Delete
  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
    Replies
    1. because you removed the table tag from the output variable in the beginning.

      Delete
  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
  15. how to pass get url id in pagination

    ReplyDelete
  16. Thanks 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

    ReplyDelete
  17. i am suffering from an error in search. when I am searching that show

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

    ReplyDelete
  18. Thanks for this project. it helped me alot.

    ReplyDelete
  19. How to convert pdo to myqli?

    ReplyDelete
  20. Thank for wonderful work, please can i have the source code for this tutorial.

    ReplyDelete
  21. when 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])
    But the design will change too but you can't click the active page number to prevent error.

    ReplyDelete
  22. 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

    ReplyDelete
  23. kumarrajesh1rs@gmail.com
    kindly 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

    ReplyDelete
  24. Hello! ... Can I run a database?

    ReplyDelete
  25. I have fix it when click 2 time on the same page number by edit:
    < 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;
    }

    ReplyDelete
  26. Hello! How can I set it to search after entering 3 characters?

    ReplyDelete
  27. i use my own solution..but it work..
    the 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();
    }
    });

    ReplyDelete
  28. Please how can i let this support arabic search ,

    ReplyDelete