Tuesday 19 February 2019

Laravel Date Range Search using Ajax jQuery



In this post, We are going to learn how to search database data which has come between two dates. That means it will return filter data from the mysql database based on two dates input. So, this type of feature we will make in Laravel PHP framework by using Ajax jQuery. So, Data comes between two dates will be load on web page without refresh of web page, because here we will use Ajax with Laravel for Date Range Search.

Here we will use Bootstrap Datepicker plugin for choose the dates for filter data options. By using this plugin we will enable Data selection on textbox, so user can select from date and to data inputs, when he will click on button, then ajax request will be send to Laravel script, which will search data which has been come between two provided date by using BETWEEN clause. So, here we will make date range search with Bootstrap Datepicker using Ajax with Laravel framework and Mysql database.

If you have make any enterprise level application using Laravel framework, and in that system you have large amount of data, then you have to filter data. So filtering of data there is one option is Date Range search. Date Range search means how to get records comes between two given date. So, this type of feature here we will make in Laravel application. So, here we will search data with date range in Laravel. Below you can find step by step process for how to implement Date Range Search feature in Laravel using Ajax.




Laravel Date Range Search using Ajax jQuery


Step 1 - Database Table


Run following sql script, it will make post table in your phpmyadmin.


USE `testing`;

/*Table structure for table `post` */

DROP TABLE IF EXISTS `post`;

CREATE TABLE `post` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `post_title` text,
  `post_description` text,
  `author` varchar(255) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;


Step 2 - Laravel Database connection


Once table has been created in your database, now you have to make database connection, for this first you have to go to config/database.php and in this you have to define your database configuration.


......

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'testing'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

.....


After this you have to open .env file, and in this file also you have to define Database configuration. So, in Larave this two place we have to define Database configuration for make database connection.


.....


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

.......


Step 3 - Create Controller in Laravel (DateRangeController.php)


For create controller in Laravel. first you have to go to your command prompt, and in that you have to first run composer command. This is because laravel all dependecy has been manage by using composer. After this you have to go to working folder, and write following command.


.....

php artisan make:controller DateRangeController

.......


This command will make DateRangeController.php controller file in app/Http/Controllers folder. In this file we have make two method.

index() - This is root method of this class, it will load date_range.php file in browser.

fetch_data() -
This method has received ajax request for search data comes between two dates. In this method we have perform database operation, and it will return data in json format.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class DateRangeController extends Controller
{
    function index()
    {
     return view('date_range');
    }

    function fetch_data(Request $request)
    {
     if($request->ajax())
     {
      if($request->from_date != '' && $request->to_date != '')
      {
       $data = DB::table('post')
         ->whereBetween('date', array($request->from_date, $request->to_date))
         ->get();
      }
      else
      {
       $data = DB::table('post')->orderBy('date', 'desc')->get();
      }
      echo json_encode($data);
     }
    }
}

?>





Step 4 - View File (data_range.blade.php)


This view you have to make in resources/views folder. This view file has been used for display html output in browser. In this file we have use jQuery, Bootstrap and Datepicker librar for date selection. Here we have used Ajax request for fetch data from server. Below you can find code of this file.


<!DOCTYPE html>
<html>
 <head>
  <title>Date Range Fiter Data in Laravel 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" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/css/bootstrap-datepicker.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/js/bootstrap-datepicker.js"></script>
 </head>
 <body>
  <br />
  <div class="container box">
   <h3 align="center">Date Range Fiter Data in Laravel using Ajax</h3><br />
   <div class="panel panel-default">
    <div class="panel-heading">
     <div class="row">
      <div class="col-md-5">Sample Data - Total Records - <b><span id="total_records"></span></b></div>
      <div class="col-md-5">
       <div class="input-group input-daterange">
           <input type="text" name="from_date" id="from_date" readonly class="form-control" />
           <div class="input-group-addon">to</div>
           <input type="text"  name="to_date" id="to_date" readonly class="form-control" />
       </div>
      </div>
      <div class="col-md-2">
       <button type="button" name="filter" id="filter" class="btn btn-info btn-sm">Filter</button>
       <button type="button" name="refresh" id="refresh" class="btn btn-warning btn-sm">Refresh</button>
      </div>
     </div>
    </div>
    <div class="panel-body">
     <div class="table-responsive">
      <table class="table table-striped table-bordered">
       <thead>
        <tr>
         <th width="35%">Post Title</th>
         <th width="50%">Post Description</th>
         <th width="15%">Publish Date</th>
        </tr>
       </thead>
       <tbody>
       
       </tbody>
      </table>
      {{ csrf_field() }}
     </div>
    </div>
   </div>
  </div>
 </body>
</html>

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

 var date = new Date();

 $('.input-daterange').datepicker({
  todayBtn: 'linked',
  format: 'yyyy-mm-dd',
  autoclose: true
 });

 var _token = $('input[name="_token"]').val();

 fetch_data();

 function fetch_data(from_date = '', to_date = '')
 {
  $.ajax({
   url:"{{ route('daterange.fetch_data') }}",
   method:"POST",
   data:{from_date:from_date, to_date:to_date, _token:_token},
   dataType:"json",
   success:function(data)
   {
    var output = '';
    $('#total_records').text(data.length);
    for(var count = 0; count < data.length; count++)
    {
     output += '<tr>';
     output += '<td>' + data[count].post_title + '</td>';
     output += '<td>' + data[count].post_description + '</td>';
     output += '<td>' + data[count].date + '</td></tr>';
    }
    $('tbody').html(output);
   }
  })
 }

 $('#filter').click(function(){
  var from_date = $('#from_date').val();
  var to_date = $('#to_date').val();
  if(from_date != '' &&  to_date != '')
  {
   fetch_data(from_date, to_date);
  }
  else
  {
   alert('Both Date is required');
  }
 });

 $('#refresh').click(function(){
  $('#from_date').val('');
  $('#to_date').val('');
  fetch_data();
 });


});
</script>


Step 5 - Set Routes


Once you have completed working on code, lastly you have to set route of your controller method. For this you have to go to routes/web.php file. In this file you have to define your method route.


<?php

Route::get('/daterange', 'DateRangeController@index');
Route::post('/daterange/fetch_data', 'DateRangeController@fetch_data')->name('daterange.fetch_data');

?>


Once you have completed all above step, then you have to run your laravel application, for this you have to go to command prompt, and write following command.


php artisan serve


This command will start your laravel application, and you can access above by write followg url.


http://127.0.0.1:8000/daterange


So, this is complete step by step process for make date range search in Laravel using Ajax jQuery.


5 comments:

  1. Hello. Thanks for the tutorials. Any idea how to implement this in previews tutorials for datatables - "How to Delete or Remove Mysql Data in Laravel using Ajax"?

    ReplyDelete
  2. server responded with a status of 419 (unknown status), I am getting the this error in my console how will i Solve it

    ReplyDelete
  3. How to make button on table

    ReplyDelete
  4. sir it just give data of from date not between

    ReplyDelete