No records found in datatable modal when using date range filter

kfrancis

Complete newbie to datatable and cannot debug the issue I am having. I have a datatable with a custom date range filter, which works as expected. I then have a row click function which opens a modal based on a different query and filters it by the cell clicked value, this also works as expected as long as I don't try turn the modal results into a datatable. However when I do add the datatable class to the modal table I get no records found whenever I use the date range filter.

However, if I clear the date range filter and click a row the modal opens with a result in a datatable as I would like, it is only when I filter the datatable using the date filter and click a row that I get an issue. If I remove the modals datatable class and use the date range filter everything works as it should.

I cannot figure out why when I filter the data using the date range the modal cannot put this into a datatable, can anyone please help?

$(document).ready(function() {
        var table = $('#production3').DataTable({
            
            "serverside" : true,
            "ajax": "../data/production3.php",
            "columns": [
            { "data": "Production Date" },
            { "data": "Patio" }
            ],
             
            responsive: true,
 
            dom: "<'toprow'<'col-md-6'B<'toolbar'>> + <'col-md-6'f>>rt<'bottomrow'<'col-md-6'l><'col-md-6'p>>",
 
            paging:   true,
            info:     false,
            fixedHeader: true,
            pageLength: 10,
            lengthMenu: [ [10, 15, 20, -1], [10, 15, 20, "All"] ],
 
        });
 
$('#production3').on('click', 'tr', function () {
        var $id1 = table.row(this).data()['Production Date'];
    $('.modal-content').load('production3_0.php?id1='+$id1, function(){
        $('#epw_modal').modal({show:true});
    });
});
 
$("div.toolbar").html('<input class="form-control input-sm" id="date_range" type="text" size="30" placeholder="Select Date Range">'); 
 
$("#date_range").daterangepicker({
    autoUpdateInput: false,
    locale: {
        "cancelLabel": "Clear",
        format: 'YYYY-MM-DD'
        },
            ranges: {
           'This Week': [moment().startOf('week'), moment().endOf('week')],
           'Next Week': [moment().add(7, 'days').startOf('week'), moment().add(7, 'days').endOf('week')],
}
});
 
$("#date_range").on('apply.daterangepicker', function(ev, picker) {
      $(this).val(picker.startDate.format('YYYY-MM-DD') + ' to ' + picker.endDate.format('YYYY-MM-DD'));
      table.draw();
});
 
$("#date_range").on('cancel.daterangepicker', function(ev, picker) {
      $(this).val('');
      table.draw();
});
});
 
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
     
    var grab_daterange = $("#date_range").val();
    var give_results_daterange = grab_daterange.split(" to ");
    var filterstart = give_results_daterange[0];
    var filterend = give_results_daterange[1];
    var iStartDateCol = 0; //using column 2 in this instance
    var iEndDateCol = 0;
    var tabledatestart = aData[iStartDateCol];
    var tabledateend= aData[iEndDateCol];
     
    if ( !filterstart && !filterend )
    {
        return true;
    }
    else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && filterend === "")
    {
        return true;
    }
    else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isAfter(tabledatestart)) && filterstart === "")
    {
        return true;
    }
    else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && (moment(filterend).isSame(tabledateend) || moment(filterend).isAfter(tabledateend)))
    {
        return true;
    }
    return false;
});

$('.def_modal').DataTable({

            dom: "<'toprow'<'col-md-6'B><'col-md-6'f>>rt<'bottomrow'<'col-md-12'p>>",

            buttons: ['copyHtml5','excelHtml5'],

        paging:   true,
            info:     false,
                fixedHeader: true,
                pageLength: 10,
                lengthMenu: [ [10, 15, 20, -1], [10, 15, 20, "All"] ],

});

```
<?php
include 'connect.php';
$id1 = $_GET['id1'];

<?php >

×

<?php $sql = "SELECT * FROM [dbo].[production] WHERE LEFT(CONVERT(VARCHAR,[Production Date],120),10) = '$id1' "; $stmt = sqlsrv_query( $conn, $sql ); if( $stmt === false) { die( print_r( sqlsrv_errors(), true) ); } while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) { ?> <?php } ?>

Load Code	Job Number	Patios
<?php echo $row['Load Code'] ?>	<?php echo $row['Job Number'] ?>	<?php echo $row['Patio'] ?>

``` ?>

crwdzr

It's because you have serverside processing enabled on the table I think.

I would recommend this change:

"ajax": "../data/production3.php"

to

"ajax": {
    url: "../data/production3.php",
    type: "POST",
    data: args=> {
        args.start = "some start date"
        args.end = "some end date"

        return args
    }
}

Then, on your serverside script, add your filtering logic to your SQL query.
What this is doing is adding your own custom POST parameters to the ajax request datatables sends, in addition to its normal ones. In this case, they would be accessed with $_POST['start'] and $_POST['end'] but you can call them whatever you want.

kfrancis

Thanks for reply. What exactly do you mean by "some start date" "some end date"?

Could you provide an example with my script as to how i add these to my modal sql query?

kfrancis

If I leave out the class that references the modals own datatable everything works with the date filter and results are seen on modal when clicking a particular date in datatable...

<table width="100%" class="table table-striped table-bordered table-hover nowrap">

but as soon as a add in the class for the datatable I get no records found...

<table width="100%" class="table pop_modal table-striped table-bordered table-hover nowrap">

Below is the datatable js being referenced...

$('.pop_modal').DataTable({

        dom: "<'toprow'<'col-md-6'B><'col-md-6'f>>rt<'bottomrow'<'col-md-12'p>>",

        buttons: ['copyHtml5','excelHtml5'],

    paging:   true,
        info:     false, 
        fixedHeader: true,
        pageLength: 10,
        lengthMenu: [ [10, 15, 20, -1], [10, 15, 20, "All"] ],

});

the strange thing is that if a don't apply any date filters and just leave the date inputs empty and click a row date the modal with reference to the pop_modal datatable works and shows results or if I use the default search box to filter rows and then click one it works fine, so what is the custom date filter doing to cause modal query to be unable to display results in the pop_modal datatable?

kfrancis

SOLVED: I need to add the date field I was filtering on the main datatable as a field within the modal datatable query.