How to retrieve data according to date range from mysql database using ajax in laravel php?

Question

I am building a simple holiday management system which allows the user to perform CRUD operation and to retrieve data according to a date range. However my CRUD operation is working as expected. But I am not able to retrieve the data according to user selected date range from an ajax call. Also this is the very first application that am developing by using laravel framework as well as ajax. I hope someone here may identify the reason for the problem am having with my code and will be able to help me.

index.blade.php

 <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-text"> to </div>
       <input type="text"  name="to_date" id="to_date" readonly class="form-control">
 </div>

ajax request handler method in HolidayController.php

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

        }
    }

web.php

<?php

Route::get('/', function () {
    return view('welcome');
});

Auth::routes();

Route::get('/home', 'HomeController@index')->name('home');

Route::Resource('holiday', 'HolidayController');

make ajax request from custom_script.js

$(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('holiday.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].id + '</td>';
                    output += '<td>' + data[count].firstname + '</td>';
                    output += '<td>' + data[count].lastname + '</td>';
                    output += '<td>' + data[count].startdate + '</td>';
                    output += '<td>' + data[count].enddate + '</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();
    });

});

Answer 1

There is no problem with query,unless $request->ajax() might remove for now. And change your blade file as follows,input type should be date, not text and remove readonly attribute.

<input type="date" name="from_date" id="from_date" class="form-control">
   <div class="input-group-addon to-text"> to </div>
<input type="date"  name="to_date" id="to_date" class="form-control">

And make sure that date data is valid data with database

For demonstration

$(document).ready(function() {


    function fetch_data(from_date = '', to_date = '') {
        if(from_date != '' && to_date != '') {
               console.log(from_date+' | '+to_date)
        }
       
    }

    $('#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 src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div class="input-group input-daterange">
       <input type="date" name="from_date" id="from_date" class="form-control">
       <div class="input-group-addon to-text"> to </div>
       <input type="date"  name="to_date" id="to_date" class="form-control">
       <button id="filter">filter</button>
       <button id="refresh">refresh</button>
 </div>

Answer 2

1. First of all, you can't use {{ route('holiday.fetch_data')}} inside your .js file. Use the simple route by typing the url or somehow, retrieve route from .blade.php file into .js file. e.g. using data-* attributes.

in #filter element pass the route with date-route="{{ route('holiday.fetch_data') }}"

and afterwards, in $('#filter').click(function() do

var route = $(this).data('route');

2. Please make sure your ajax request reach in your fetch_data in your Controller. In order to do that, just write inside your fetch_data

dd($request->all());

If you get the requests from ajax, means that your connections with fetch_data work just fine.

3. Try parsing the dates with Carbon, as I'm doing right below. Also, remove $request->ajax() and return json data with return.

use Carbon\Carbon;

public function fetch_data(Request $request)
{     
   if($request->from_date != '' && $request->to_date != '') {
        $from = Carbon::parse($request->from_date)->toDateString();
        $to = Carbon::parse($request->to_date)->toDateString();

       $data = DB::table('holidays')->whereBetween('startdate',[$from, $to])->get();
   }else {
       $data = DB::table('holidays')->orderBy('startdate', 'desc')->get();
   }

   return json_encode($data);
}