DataTables Returns Incorrect Results

fukie

Hi All,

I'm using Tigrang CakePHP datatables plugin + Datatables 1.10.0 in my application. I understand it is not fully supported but I hope someone could point me in the right direction of finding a solution as I can't seem to find any related issue anywhere much on the Internet that could help me. After 1 week of debugging, I've resorted to asking around here as I feel it is more of a configuration issue somewhere rather than the plugin or DT problem. But I'm really dying being unable to fix this problem...

I've implemented the DT and the plugin throughout my site without much problems. All of them works except one module where when I search, it would return incorrect results. For example, when I search for 'bus', it returns a lot of irrelevant results which do not have bus in it. I checked the tags, coding and stuff but I can't seem to find any issues there.

The table also looks alright, nothing out of the ordinary:

id - int(11)
date - date
category_1 - int(11)
category_2 - int(11)
category_3 - int(11)
payee - varchar(255)
description - text
remarks - text
amount - decimal(10,2)
tax_deductible - (10,2)
status - varchar(255)
name - varchar(255)

Description and remarks do contain special characters like newline but I previously did not have any problems with them and datatables.

This is the JS code to load DT.

<script type="text/javascript">
$(document).ready(function() {
    $('#myexample').dataTable( {
        
        "order": [[ 0, "desc" ]],
        "iDisplayLength": 25,
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": '<?php echo $this->Html->url(array("action" => "index", "?" => array("model" => "AExpenseTest"))); ?>'
    } );
} );
</script>

Looking at the Ajax JSON results, it validates without any errors.

This is the the AExpenseTest/datatable/index.ctp which contains the processing of the results:

<?php
    
    foreach($dtResults as $result) {

        if($result['AExpenseTest']['remarks'] != '') {
            $remarks = "Remarks";
        } else {
            $remarks = "";
        }

        if($result['AExpenseTest']['tax_deductible'] == 1) {
            $tax_deductible = "Yes";
        } else {
            $tax_deductible = "No";
        }

        foreach ($category as $key => $value) {
            if ($result['AExpenseTest']['category_1'] == $key) {
                $category_1 = $value;
            }
            if ($result['AExpenseTest']['category_2'] == $key) {
                $category_2 = $value;
            }
            
        }
        
        $actionButtons =  $this->Html->link($this->Html->div('btn-group btn-group-xs btn btn-default',$this->Html->tag('span', '', array('class' => 'glyphicon glyphicon-search'))), array('action' => 'view', $result['AExpenseTest']['id']), array('escapeTitle' => false, 'title' => 'View'))." ".
                $this->Html->link($this->Html->div('btn-group btn-group-xs btn btn-default',$this->Html->tag('span', '', array('class' => 'glyphicon glyphicon-pencil'))), array('action' => 'edit', $result['AExpenseTest']['id']), array('escapeTitle' => false, 'title' => 'Edit'));
           
            

        $this->dtResponse['aaData'][] = array(
            $result['AExpenseTest']['date'],
            $category_1,
            $category_2,
            
            $result['AExpenseTest']['payee'],
            $result['AExpenseTest']['description'],
            $remarks,
            $result['AExpenseTest']['amount'],          
            $tax_deductible,
            $result['AExpenseTest']['status'],
            
           
            
            $actionButtons,
            
        );
    }
?>

I hope this is sufficient information. I'm unable to provide a login to allow you guys (& ladies) to test as this is on my localhost.

For your kind assistance
fukie

fukie

I have fixed the issue with help from the plugin developer.
For reference - https://github.com/tigrang/cakephp-datatable/issues/55#issuecomment-72766633

Thanks.