Datatables (Server-side processing) in Typo3 with

Question

I'm trying to implement https://www.datatables.net/examples/data_sources/server_side.html into Typo3 (6.2LTS) with a flexible content element and templavoila. The result is a functioning but empty (No data available in table) table at the moment. I'm using the following php script:

<?php

class custom_datatable {  

var $datatable;    // reference to the calling object.  

function custom_table1($columns,$conf)    
{   

    global $TSFE;        
    $TSFE->set_no_cache();

    //do whatever you want here

    //db verbindung
    mysql_connect("my_host", "my_user", "my_password");
    mysql_select_db("my_database");

    /*
     * DataTables example server-side processing script.
     *
     * Please note that this script is intentionally extremely simply to  show how
     * server-side processing can be implemented, and probably shouldn't be used as
     * the basis for a large complex system. It is suitable for simple use cases as
     * for learning.
     *
     * See http://datatables.net/usage/server-side for full details on the server-
     * side processing requirements of DataTables.
     *
     * @license MIT - http://datatables.net/license_mit
     */

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */

    // DB table to use
    $table = 'my_table';

    // Table's primary key
    $primaryKey = 'id';

    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case simple
    // indexes
    $columns = array(
        array( 'db' => 'Field1', 'dt' => 0 ),
        array( 'db' => 'Field2', 'dt' => 1 ),
        array( 'db' => 'Field3', 'dt' => 2 ),
        array( 'db' => 'Field4', 'dt' => 3 ),
        array( 'db' => 'Field5', 'dt' => 4 ),
        array( 'db' => 'Field6', 'dt' => 5 )

    );

    return $columns; 

}      
} 
?>

And get the following result in the source code:

 <table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Field1</th>
            <th>Field2</th>
            <th>Field3</th>
            <th>Field4</th>
            <th>Field5</th>
            <th>Field6</th>
        </tr>
    </thead>

    <tfoot>
        <tr>
            <th>Field1</th>
            <th>Field2</th>
            <th>Field3</th>
            <th>Field4</th>
            <th>Field5</th>
            <th>Field6</th>
        </tr>
    </tfoot>
</table>

<script type="text/javascript">

    $(document).ready(function() {
        $('#example').dataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": "Array"
        } );
    } );

    </script>

What am I doing wrong or is missing?

Answer 1

in order for the server side processing to work, you must pass the right data format into it,

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [
    [
      "Airi",
      "Satou",
      "Accountant",
      "Tokyo",
      "28th Nov 08",
      "$162,700"
    ],
    [
      "Angelica",
      "Ramos",
      "Chief Executive Officer (CEO)",
      "London",
      "9th Oct 09",
      "$1,200,000"
    ]
  ]
}

then you should also check the ssp class found on github for the server side-processing query https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

for additional information please visit http://legacy.datatables.net/usage/server-side

You should use it like this one:

in your datatables initialization

var your_datatable_variable_here = $('#your_datatable_id').dataTable({
responsive:true,
"bFilter": true,
"oLanguage": {
"sProcessing": "link_to_your_image_processing_gif/img/ajax-loader.gif'>"
},
"autoWidth" : true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "your_php_file_here.php"

})

PHP FIle:

function _dataTableServerSide($iQuery,$aColumns,$dReturnType){
    $iDisplayStart = $this->input->get_post('iDisplayStart', true);
    $iDisplayLength = $this->input->get_post('iDisplayLength', true);
    $iSortCol_0 = $this->input->get_post('iSortCol_0', true);
    $iSortingCols = $this->input->get_post('iSortingCols', true);
    $sSearch = $this->input->get_post('sSearch', true);
    $sEcho = $this->input->get_post('sEcho', true);
    $sLimit = "";

    if(isset($iDisplayStart) && $iDisplayLength != '-1'){
        $sLimit = "LIMIT ".$iDisplayStart.", ".$iDisplayLength; //reverse execution of limit in sql
    }


    if(isset($iSortCol_0)) {
    $sOrder = "ORDER BY  ";
        for($i=0; $i<intval($iSortingCols); $i++) {
        $iSortCol = $this->input->get_post('iSortCol_'.$i, true);
        $bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
        $sSortDir = $this->input->get_post('sSortDir_'.$i, true);

            if($bSortable == "true") {
                $sOrder .= $aColumns[intval($iSortCol)]." ".$sSortDir;
            }
        }
    }

    $sWhere = "";
    if(isset($sSearch) && !empty($sSearch)) {
        $sWhere = "WHERE (";
            for($i=0; $i<count($aColumns); $i++) {
            $bSearchable = $this->input->get_post('bSearchable_'.$i, true);

                if(isset($bSearchable) && $bSearchable == 'true') {
                    $sWhere .= $aColumns[$i]." LIKE '%".$sSearch."%' OR ";
                }
            }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ")";
    }


    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( $this->input->get_post('bSearchable_'.$i, true) == "true" && $this->input->get_post('sSearch_'.$i, true) != '' ) {
            if ( $sWhere == "" ) {
                $sWhere = "WHERE ";
            }
            else {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".$this->input->get_post('sSearch_'.$i, true)."%' ";
        }
    }

    switch($dReturnType) {
        case 1: {
            $sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).
            " FROM (".$iQuery.") ".$sWhere." ".$sOrder." ".$sLimit;
            $rResult = $this->db->query($sQuery);

            $sQuery = "SELECT FOUND_ROWS() found_rows";
            $iFilteredTotal = $this->db->query($sQuery)->row()->found_rows;

            $sQuery = "SELECT COUNT(*) counter FROM (".$iQuery.") ";
            $iTotal = $this->db->query($sQuery)->row()->counter;
        } break;
        case 2: {
            $sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).
            " FROM (".$iQuery.") AA ".$sWhere." "."ORDER BY gl_sub_id ASC LIMIT 1,10";//$sOrder." ".$sLimit;
            $rResult = $this->db->query($sQuery);

            $sQuery = "SELECT FOUND_ROWS() found_rows";
            $iFilteredTotal = $this->db->query($sQuery)->row()->found_rows;

            $sQuery = "SELECT COUNT(*) counter FROM (".$iQuery.") AA";
            $iTotal = $this->db->query($sQuery)->row()->counter;
        }
    }

    $output = array(
        'sEcho' => intval($sEcho),
        'iTotalRecords' => $iTotal,
        'iTotalDisplayRecords' => $iFilteredTotal,
        'aaData' => array()
    );



    foreach($rResult->result_array() as $aRow) {
    $row = array();

        foreach($aColumns as $col) {
            $row[] = $aRow[$col];
        }

    $output['aaData'][] = $row;
    }

    return $output;
}

note: this is a working example, I am using code igniter as the base framwork,and MySQL as the database, if you want to convert it to PHP, just replace the code igniter functions with the standard php $GET methods

you will need to $GET the following from the client to make it work.

$iDisplayStart = $this->input->get_post('iDisplayStart', true);
$iDisplayLength = $this->input->get_post('iDisplayLength', true);
$iSortCol_0 = $this->input->get_post('iSortCol_0', true);
$iSortingCols = $this->input->get_post('iSortingCols', true);
$sSearch = $this->input->get_post('sSearch', true);
$sEcho = $this->input->get_post('sEcho', true);
$iSortCol = $this->input->get_post('iSortCol_'.$i, true);
$bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
$sSortDir = $this->input->get_post('sSortDir_'.$i, true);
$bSearchable = $this->input->get_post('bSearchable_'.$i, true);

and this is where the data is processed to be passed back to client page

$output = array(
    'sEcho' => intval($sEcho),
    'iTotalRecords' => $iTotal,
    'iTotalDisplayRecords' => $iFilteredTotal,
    'aaData' => array()
);



foreach($rResult->result_array() as $aRow) {
$row = array();

    foreach($aColumns as $col) {
        $row[] = $aRow[$col];
    }

$output['aaData'][] = $row;
}

return $output;