Ultimate Date Time Search Not Working

vincmeister

Hi Allan,

I'm using your ultimate datetime plugins
The sort function working well, but not on search

My code:

$(function () {
            $('#min_date').datetimepicker({
                format: 'DD MMM YYYY'
            });
    });
    $(function () {
            $('#max_date').datetimepicker({
                format: 'DD MMM YYYY'
            });
    });
        
    $(document).ready(function() {
        //define custom date format
        $.fn.dataTable.moment('DD MMM YYYY');
        
        // Setup - add a text input to each footer cell
        $('#tblProcessDocument tfoot th').each( function () {
            var visIdx = $(this).index();
            var title = $(this).text();
            if (title !==""){
                $(this).html( 
                '<input class="text-warning col-lg-12 col-md-12" type="text" placeholder="'+title+'">'
                );
            }
        } );
        
        var tblProcessDocument = $('#tblProcessDocument').DataTable({
            serverSide: true,
            scrollX: true,
            mark: true,
            ajax: "function/log_process_document.php",
            columnDefs: [
                {
                    //render: $.fn.dataTable.render.moment( 'YYYY-MM-DD', 'DD MMM YYYY'),
                    //"targets": 3
                }
            ],
            buttons: [
                { extend: "copy",  text: '<u>C</u>opy'},
                { extend: "excel",  key: 'x' ,text: 'E<u>x</u>cel'},
                { extend: "pdf",  key: 'f' ,text: 'pd<u>f</u>'}
            ],
            createdRow: function( row, data, index ) {
                if ( data[5] == null ) 
                {
                    $('td', row).eq(5).addClass('bg-red');
                }
            }
        });
        
        $('#min_date').keyup( function() { tblProcessDocument.draw(); } );
        $('#max_date').keyup( function() { tblProcessDocument.draw(); } );

        tblProcessDocument.columns().eq(0).each( function ( colIdx ) {
     $( 'input', 'th:nth-child('+(colIdx+1)+')' ).on( 'keyup change', function() {
            tblProcessDocument
                .column( colIdx )
                .search( this.value )
                .draw();
     });
        });
        
    } );

please help, this is the live example
thank you
danny

vincmeister

I found the problem on serverSide: true
if the not using serverSide true, the filter works
But the data slowly loaded. For now i have 5428 rows
I'm using SSP Class. Please advise, thank you

allan

If you are using server-side processing, all search, sort and paging is done at the client-side. A client-side sorting or search plug-in isn't going to do anything at all.

It sounds like the data in the server-side database might not be in a date column? If so, that's the issue.

Allan

vincmeister

Hi Allan,

I'm using formatter

array(
            'db'        => 'date_field',
            'dt'        => 5,
            'formatter' => function( $d, $row ) {
                if ($d === "0000-00-00"){
                    echo "";
                }else{
                    return date( 'd M Y', strtotime( $d ) );
                }
            }

The source came from mysql view
Please advise, thank you

allan

And what is the data type of the column date_field in the SQL database?

vincmeister

the data_type is date

allan

Thanks. So the data in the database is stored as ISO8601 (e.g. 2017-10-29). So only a search in that format would work as expected. If you search in the format that you are rendering the date into in PHP, then it won't work, because the date isn't in that format where the search is being done.

You'd probably need to convert the date to search for into ISO8601 before the SQL WHERE condition is applied.

Allan