I'm trying to implement column based filtering in Data tables with editor php librray

joshuanulton

This is my debug code debug code: uqaxum
The default search functionality as well as the column based filtering does not work. The pagination and other function work as expected.
My PHP Class is

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tt_tracking' )
    ->fields(
        Field::inst( 'inbound_date' )->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
        Field::inst( 'inbound_time' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'license_plate' ),
        Field::inst( 'truck_number' ),
        Field::inst( 'company_name' ),
        Field::inst( 'driver_name' ),
        Field::inst( 'destination' ),
        Field::inst( 'purpose' ),
        Field::inst( 'id_verified' ),
        Field::inst( 'id_comments' ),
        Field::inst( 'add_passengers' )->validator( 'Validate::numeric' ),
        Field::inst( 'add_comments' ),
        Field::inst( 'outbound_date' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
        Field::inst( 'outbound_time' ),
        Field::inst( 'outbound_passengers' )->validator( 'Validate::numeric' ),
        Field::inst( 'transcription_stage' )->validator( 'Validate::numeric' ),
        Field::inst( 'license_plate_comments' ),
        Field::inst( 'inbound_audio_id' ),
        Field::inst( 'outbound_audio_id' )
        
    )
    ->where( function ( $q ) {
        $q
          ->where( 'transcription_stage','3','=' )
          ->or_where( 'transcription_stage','5','=' );
      } )
    ->process( $_POST )
    ->json();
    
?>

My JS Code is

$('#trackingInfo thead th:not(:first-child)').each( function () {
        var title = $('#trackingInfo thead th').eq( $(this).index() ).text();
        $(this).append( '<br/><input name="columns['+$(this).index()+ '][search][value]" type="text" placeholder="Search '+title+'" />' );
    } );
    var dt =$('#trackingInfo').DataTable( {
        dom: "Trft<li>p",
        ajax: {
            url:"model/Client.php?wcol=transcription_stage&wvalue=5&scol=transcription_stage&svalue=3",
            type: "POST"
        },
        serverSide: true,
        "scrollX": true,
        "processing": true,
        "bLengthChange": true,
        "lengthMenu": [[11, 33, 121], [11, 33, 121]],
        columns: [
            {
                "class":          "details-control",
                "orderable":      false,
                "data":           null,
                "defaultContent": "",
                searchable:false
            },
            {'data':'inbound_date'},
            {'data':'inbound_time'},
            {'data':'license_plate','name':'license_plate'},
            {'data':'company_name'},
            {'data':'driver_name'},
            {'data':'destination'},
            {'data':'outbound_date'},
            {'data':'outbound_time'},
            
        ],
        order: [ 1, 'desc',2,'desc' ],
        tableTools: {
            
            sSwfPath: "swf/copy_csv_xls_pdf.swf",
            aButtons: [
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        },
         
        "columnDefs": [ {
            "render": function ( data, type, row ) {
                return data == "00:00:00" ? "" : data;
            },
            "targets": 8
            
        },
        {
            "render": function ( data, type, row ) {
                return data == "0000-00-00" ? "" : data;
            },
            "targets": 7
            
        } ]
    } );
    var detailRows = [];
 
    $('#trackingInfo tbody').on( 'click', 'tr td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = dt.row( tr );
        var idx = $.inArray( tr.attr('id'), detailRows );
 
        if ( row.child.isShown() ) {
            tr.removeClass( 'details' );
            row.child.hide();
 
            // Remove from the 'open' array
            detailRows.splice( idx, 1 );
        }
        else {
            tr.addClass( 'details' );
            //console.log(format( row.data() ));
            var child = row.child(format( row.data() ),'child-row' );
            //console.log(child);
            child.show();
 
            // Add to the 'open' array
            if ( idx === -1 ) {
                detailRows.push( tr.attr('id') );
            }
        }
    } );
 
    
    // On each draw, loop over the `detailRows` array and show any child rows
    dt.on( 'draw', function () {
        $.each( detailRows, function ( i, id ) {
            $('#'+id+' td.details-control').trigger( 'click' );
        } );
    } );
    dt.columns().every( function () {
        var that = this;
 
        $( 'input', this.header() ).on( 'keyup change', function (e) {
            e.stopPropagation();
            that
                .search( this.value )
                .draw();
            return false;
        } );
        $( 'input', this.header() ).on('click',function(e){
            e.stopPropagation();
            return false;
        });
    } );
} );
function format ( d ) {
    var ret = '<ul><li>Truck Number: '+d.truck_number+'</li>'+
        '<li>Purpose: '+ d.purpose + '</li>'+
        '<li>ID Verified: '+ d.id_verified + '</li>'+
        '<li>ID Comments: '+ d.id_comments + '</li>'+
        '<li>Additional Comments: '+ d.add_comments + '</li>'+
        '<li>Inbound Passengers: '+ d.add_passengers + '</li>'+
        '<li>Outbound Passengers: '+ d.outbound_passengers+ '</li></ul>';
    return ret;
}

My HTML code is

<table id="trackingInfo" width='100%' class="display table table-bordered" cellspacing="0" >
                    <thead>
                        <tr>
                            <th style='width:3%'></th>
                            <th style='width:10%'>Inbound Date</th>
                            <th style='width:5%'>Inbound Time</th>
                            <th style='width:5%'>License Plate</th>
                            <th>Company Name</th>
                            <th>Driver Name</th>
                            <th>Destination</th>
                            <th style='width:10%'>Outbound Date</th>
                            <th style='width:5%'>Outbound Time</th>
                        </tr>
                    </thead>
                    
                </table>

allan

I don't immediately see anything wrong with your code. Any chance of a link to the page so I can take a look at the client-side code live?

I've just tried a sanity check with my example and if you run:

$('#example').DataTable().column(0).search('a').draw()

in the console it should work okay.

Allan

joshuanulton

Thanks for the response :). I think I found the Issue, I'm using where clause in Client.php, If i remove this where every thing seems to be working fine. Any Idea I can add where as well

->where( function ( $q ) {
    $q ->where( 'transcription_stage','3','=' );
 } )

allan

Hi,

Wow - sorry I lost track of this thread! Is this still causing issues for you?

The where() that you have looks like it should work correctly. So I understand correctly - if you comment that out, column filtering works? If you leave it in, it works (limits the table based on that expression) but when you enter column filtering information it stops working (results zero results I presume)?

Allan