Saturday 17 November 2018

Laravel Searching Column Sorting with Pagination using Ajax

Laravel Sorting with Pagination using Ajax




Laravel Searching with Sorting with Pagination using Ajax




If you are beginner level in Laravel, and if you want to learn How to use Ajax with Laravel, then you have come on right place because in this post you can find How to Live data search with Sort table column data in ascending or descending order and Pagination in Laravel framework by using Ajax.  We have always try to publish something new post which will help you to learn something new. So, here we have publish new article on Laravel with Ajax and make simple mysql table data live searching, table column sorting data in Ascending or descending order with pagination link. This is amazing tutorial of Laravel tutorial series with Ajax and here we have make search filter with sortable table and pagination link. Which is one part of any crud application. So, by using this tutorial you can make simple application in which can live search mysql data, sort tabular data and pagination link in Laravel framework using Ajax. In short we will do column sorting with Live data search in Laravel using Ajax.

We all know by using Data sorting and searching is important for find quick and perfect data in a very short time. Here we will take a live example of data sorting in ascending or descending order with pagination. Here we will not only implement tabular data sorting but also we will also make pagination link with column sorting in Laravel using Ajax. We will discuss step by step this ascending or descending column sorting of data with pagination using Ajax and Laravel. This tutorial will show you how to create data sorting with pagination operation without refresh of web page with Laravel jQuery and Ajax.





Step 1 : Instal Laravel Application


Here we have start from scratch. So, If you have not install Laravel application, then you have to open command prompt, and run composert command, and then after you can run below command this command will download Laravel application, and install at define destination.


composer create-project --prefer-dist laravel/laravel student_crud


Step 2 - Database Connection


Once Laravel application has been installed successfully, then first you have to make database connection. For make database connection you have to .env file and define following database configuration.


DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=


Step 3 - Create PaginationController (Controller)


After making database connection, we have to make one PaginationController file under app/Http/Controllers/PaginationController.php. This controller will handle http request for display data on web page with data sorting and pagination request.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class PaginationController extends Controller
{
    function index()
    {
     $data = DB::table('post')->orderBy('id', 'asc')->paginate(5);
     return view('pagination', compact('data'));
    }

    function fetch_data(Request $request)
    {
     if($request->ajax())
     {
      $sort_by = $request->get('sortby');
      $sort_type = $request->get('sorttype');
            $query = $request->get('query');
            $query = str_replace(" ", "%", $query);
      $data = DB::table('post')
                    ->where('id', 'like', '%'.$query.'%')
                    ->orWhere('post_title', 'like', '%'.$query.'%')
                    ->orWhere('post_description', 'like', '%'.$query.'%')
                    ->orderBy($sort_by, $sort_type)
                    ->paginate(5);
      return view('pagination_data', compact('data'))->render();
     }
    }
}
?>


Step 4 - Create View file


For display data on web page, in Laravel we have to create view file in resources/views folder. In this folder we have to create two view files like pagination.blade.php and pagination_data.blade.php.

In pagination.blade.php you can find complete html jQuery and ajax code for data sorting and pagination. In this file we have include pagination_data.blade.php file data by using @include statement. Below you can find source code of both view files.




resources/views/pagination.blade.php

<!DOCTYPE html>
<html>
 <head>
  <title>Laravel Live Data Search with Sorting & Pagination using Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h3 align="center">Laravel Live Data Search with Sorting & Pagination using Ajax</h3><br />
   <div class="row">
    <div class="col-md-9">

    </div>
    <div class="col-md-3">
     <div class="form-group">
      <input type="text" name="serach" id="serach" class="form-control" />
     </div>
    </div>
   </div>
   <div class="table-responsive">
    <table class="table table-striped table-bordered">
     <thead>
      <tr>
       <th width="5%" class="sorting" data-sorting_type="asc" data-column_name="id" style="cursor: pointer">ID <span id="id_icon"></span></th>
       <th width="38%" class="sorting" data-sorting_type="asc" data-column_name="post_title" style="cursor: pointer">Title <span id="post_title_icon"></span></th>
       <th width="57%">Description</th>
      </tr>
     </thead>
     <tbody>
      @include('pagination_data')
     </tbody>
    </table>
    <input type="hidden" name="hidden_page" id="hidden_page" value="1" />
    <input type="hidden" name="hidden_column_name" id="hidden_column_name" value="id" />
    <input type="hidden" name="hidden_sort_type" id="hidden_sort_type" value="asc" />
   </div>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){

 function clear_icon()
 {
  $('#id_icon').html('');
  $('#post_title_icon').html('');
 }

 function fetch_data(page, sort_type, sort_by, query)
 {
  $.ajax({
   url:"/pagination/fetch_data?page="+page+"&sortby="+sort_by+"&sorttype="+sort_type+"&query="+query,
   success:function(data)
   {
    $('tbody').html('');
    $('tbody').html(data);
   }
  })
 }

 $(document).on('keyup', '#serach', function(){
  var query = $('#serach').val();
  var column_name = $('#hidden_column_name').val();
  var sort_type = $('#hidden_sort_type').val();
  var page = $('#hidden_page').val();
  fetch_data(page, sort_type, column_name, query);
 });

 $(document).on('click', '.sorting', function(){
  var column_name = $(this).data('column_name');
  var order_type = $(this).data('sorting_type');
  var reverse_order = '';
  if(order_type == 'asc')
  {
   $(this).data('sorting_type', 'desc');
   reverse_order = 'desc';
   clear_icon();
   $('#'+column_name+'_icon').html('<span class="glyphicon glyphicon-triangle-bottom"></span>');
  }
  if(order_type == 'desc')
  {
   $(this).data('sorting_type', 'asc');
   reverse_order = 'asc';
   clear_icon
   $('#'+column_name+'_icon').html('<span class="glyphicon glyphicon-triangle-top"></span>');
  }
  $('#hidden_column_name').val(column_name);
  $('#hidden_sort_type').val(reverse_order);
  var page = $('#hidden_page').val();
  var query = $('#serach').val();
  fetch_data(page, reverse_order, column_name, query);
 });

 $(document).on('click', '.pagination a', function(event){
  event.preventDefault();
  var page = $(this).attr('href').split('page=')[1];
  $('#hidden_page').val(page);
  var column_name = $('#hidden_column_name').val();
  var sort_type = $('#hidden_sort_type').val();

  var query = $('#serach').val();

  $('li').removeClass('active');
        $(this).parent().addClass('active');
  fetch_data(page, sort_type, column_name, query);
 });

});
</script>


resources/views/pagination_data.blade.php

                                               @foreach($data as $row)
      <tr>
       <td>{{ $row->id}}</td>
       <td>{{ $row->post_title }}</td>
       <td>{{ $row->post_description }}</td>
      </tr>
      @endforeach
      <tr>
       <td colspan="3" align="center">
        {!! $data->links() !!}
       </td>
      </tr>


Step 5 - Set Route


Lastly we have to set route for controller and method which we have make under controller. For this we have to go to routes/web.php file and write following code for set route.


<?php

Route::get('/pagination', 'PaginationController@index');

Route::get('/pagination/fetch_data', 'PaginationController@fetch_data');

?>


Once you have completely follow this step then you can make data sorting and pagination in Laravel with Ajax. For run laravel application, you have to run following command.


php artisan serve


After run this command, then you will receive message like Laravel server has been start with this link - http://localhost:8000/. For run this application you have to type following link in your browser.


http://localhost:8000/pagination


23 comments:

  1. Hello, good work.

    Can you help me. I have this error undefined variable: data (View: C:\xamp72\htdocs\student_crud\resources\views\pagination_data.blade.php)
    I use the same code. Thanks

    ReplyDelete
    Replies
    1. check your pagination data blade file where you will see a loop , written data is that true to the controller that you have mention

      Delete
  2. thank you
    you are helping a lot

    ReplyDelete
  3. bonjour svp j aimerais l installer sur mon hébergement alors j aimerais savoir comment le faire par ce qu en local il faut passé par l invite de commande.

    ReplyDelete
  4. Thank you for this snippet

    ReplyDelete
  5. There is an error when we are on the last page of pagination and then search some keyword then its shows no data which is shown on the first page of pagination.

    ReplyDelete
    Replies
    1. Hello? Have you found a solution for this? I too have encountered the same situation?

      Delete
    2. Hello? Have you found a solution for this? I too have encountered the same situation?

      Delete
    3. Hello? Can you please tell me if you have solved this? I too have the same situation. Thanks

      Delete
  6. Hello Friends,
    Thank You For This Searching And Pagination Using Ajax.It's Working But Not Properly. Because When I Will Go Page No. 2 Link Pagination And Search No Any Record Found, Even After Result Is Persent This table. Can You Please Do Help Me?

    ReplyDelete
    Replies
    1. Hello? Have you found a solution for this? I too have encountered the same situation?

      Delete
    2. I have the same situation. Did you solve it? can you please help me?

      Delete
  7. pagination links not working properly.

    ReplyDelete
    Replies
    1. Hello! Have you managed to solve this?

      Delete
    2. $(document).on('click', '.pagination a', function(event){
      event.preventDefault();
      var page = $(this).attr('href').split('page=')[1];
      $('#hidden_page').val(page);
      var column_name = $('#hidden_column_name').val();
      var sort_type = $('#hidden_sort_type').val();

      var query = $('#serach').val();

      $('li').removeClass('active');
      $(this).parent().addClass('active');
      fetch_data(page, sort_type, column_name, query);
      });

      this code to change next page or previous page

      Delete
  8. can you help me i have an error
    Request URL: http://localhost/fetch_data?page=2&sortby=id&sorttype=asc&query=
    Request Method: GET
    Status Code: 404 Not Found
    Remote Address: [::1]:80
    Referrer Policy: no-referrer-when-downgrade
    please give me a solution

    ReplyDelete
  9. hello this code don'n run properly for me . when return data create duplicate page in my table

    ReplyDelete
  10. On keyup you should change page to 1.
    great job...
    Thanks

    ReplyDelete
  11. on keyup you should reset page to 1
    Great Job,
    Thanks

    ReplyDelete