Rendering via 'return' function / server side processing Datatables / Typo3 / PHP

Capamania · March 2015

Dear Datatables Team,

how do I need to adjust the 'Output' of https://datatables.net/development/server-side/php_mysql for the JSON array / or the output in general to be rendered via the 'return' function? Typo3 requires rendering via 'return' (http://docs.typo3.org/typo3cms/TyposcriptReference/ContentObjects/UserAndUserInt/Index.html).

```
<?php

class my_custom_datatable {

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

function my_custom_table1($my_output) {

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

// ** datatable script can go here  **//

 return $my_output;    // ** Typo3 requires rendering via 'return' (http://docs.typo3.org/typo3cms/TyposcriptReference/ContentObjects/UserAndUserInt/Index.html). **//

 }

}

<?php > ``` Unfortunately I can't use "echo json_encode( $output );" as in the example. When using "echo json_encode( $output );" all data (valid JSON string) is rendered at the very top of the page outside of the html tags and not in the table. I also raised the question here: http://stackoverflow.com/questions/29242553/json-array-rendered-via-return-function-php-datatables-typo3 ?>

Thanks for the help!

Capamania · April 2015

Happy Easter Everyone!

Unfortunately I'm still having trouble to render the data into the table. Please take a look at the example here:

http://www.rfpfactsheet.com/

As mentioned above, I'm trying to include https://datatables.net/development/server-side/php_mysql into Typo3. Including a php script into Typo3 requires the above structure though and rendering the data via the return function.

I'm using the php script as in the example:


<?php

class custom_datatable {  
      
    var $field_datatable;    // reference to the calling object.  
    
    function custom_table1($output)    
    {   

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

    //do whatever you want here

    /*
     * Script:    DataTables server-side script for PHP and MySQL
     * Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
     * License:   GPL v2 or BSD (3-point)
     */
     
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */
     
    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */
    $aColumns = array( 'field1', 'field2', 'field3', 'field4', 'field5', 'field6' );
     
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "field1";
     
    /* DB table to use */
    $sTable = "datatable_test_1";
     
    /* Database connection information */
    $gaSql['user']       = "my_user";
    $gaSql['password']   = "my_password";
    $gaSql['db']         = "my_db";
    $gaSql['server']     = "my_server";
     
     
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side, there is
     * no need to edit below this line
     */
     
    /*
     * Local functions
     */
    function fatal_error ( $sErrorMessage = '' )
    {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }
 
     
    /*
     * MySQL connection
     */
    if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
    {
        fatal_error( 'Could not open connection to server' );
    }
 
    if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
    {
        fatal_error( 'Could not select database ' );
    }
     
     
    /*
     * Paging
     */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
            intval( $_GET['iDisplayLength'] );
    }
     
     
    /*
     * Ordering
     */
    $sOrder = "";
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
            }
        }
         
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }
     
     
    /*
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
            }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
     
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
        }
    }
     
     
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
     
    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
     
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];
     
     
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
     
    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
    
    echo json_encode( $output );
    
   }      
 } 


<?php
>
```
And my html looks like this: 
?>

<html>

<body>

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6
Field 1	Field 2	Field 3	Field 4	Field 5	Field 6

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

</body>

</html>

```

Any help would be much appreciated! After all, its Easter! :-)

farr433 · April 2015

My code is properly returning the data server side so try to use that to help you out. I use laravel framework so some of it will have to be changed...

For example where you see " {{ code here }} " is really <?php echo 'code here' ?> but you should be able to figure it out.

https://www.datatables.net/forums/discussion/27017/filtered-data#latest

Capamania · April 2015

Thanks for your example farr433. Obviously I'm new to php and I'm struggling to get it running. Any additional help/hint would be much appreciated!

tangerine · April 2015

Did you ask the Typo3 people? They have a plug-in which facilitates DataTables.

Capamania · April 2015

Thanks tangerine. I have the http://typo3.org/extensions/repository/view/cag_tables extension running, but unfortunately it's not serverside processing. My data load is to big (>50000 rows) and I have a performance issue, that's why I would like to have the serverside processing ...

Capamania · April 2015

In meantime I managed to render the data at the right place. The trick was I changed the rendering to ...

return json_encode( $output );

... and the data is now rendered at the marker, but unfortunately still not in the table. CRY. Live example (incl. syntax showcase):

http://www.rfpfactsheet.com/rfp-issuer/a001-finance-factsheet/rfp-id-a001/live-example/

Syntax used:

<script type="text/javascript">
$(document).ready(function() {
$('#example').dataTable( {
"processing": true,
"serverSide": true,
"ajax": {"sEcho":0,"iTotalRecords":"6","iTotalDisplayRecords":"6","aaData":[["1","Hans","Meier","51","2011-04-13","EUR200"],["2","Frank","Heinz","45","2004-02-17","EUR60"],["3","Katrin","Kohl","35","2011-08-17","EUR1000"],["4","Werner","Pertl","39","2013-11-19","USD499"],["5","Christina","Sinn","22","2015-03-09","GBP99"],["6","Klaus","Vienna","67","1991-01-15","EUR5000"]]}
} );
} );
</script>

Error message:
"Invalid JSON response" although jsonlint.com says it's valid. CRY again

I also activated the caching again, but deleted // var $field_datatable;

But why is the data still not in the table?

allan · April 2015

Doesn't look like JSON to me:

<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js ie6 oldie"> <![endif]-->
<!--[if IE 7]> <html class="no-js ie7 oldie"> <![endif]-->
<!--[if IE 8]> <html class="no-js ie8 oldie"> <![endif]-->
<!--[if gt IE 8]> <!--><html class="no-js"> <!--<![endif]-->
<head>

<meta charset="utf-8">
<!-- 
    This side was created by:
....

Looks like HTML :-). In fact it looks like the same page again.

Allan

Capamania · April 2015

Hi Allan! What do I need to do?! :-)

... php is rendering the following:

{"sEcho":0,"iTotalRecords":"6","iTotalDisplayRecords":"6","aaData":[["1","Hans","Meier","51","2011-04-13","EUR200"],["2","Frank","Heinz","45","2004-02-17","EUR60"],["3","Katrin","Kohl","35","2011-08-17","EUR1000"],["4","Werner","Pertl","39","2013-11-19","USD499"],["5","Christina","Sinn","22","2015-03-09","GBP99"],["6","Klaus","Vienna","67","1991-01-15","EUR5000"]]}

... and per www.jsonlint.com it's a "Valid JSON".

In the data source of http://www.rfpfactsheet.com/rfp-issuer/a001-finance-factsheet/rfp-id-a001/live-example/ you see the output on line 166 right next to the "ajax" call.

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

I used a ###TYPO3 MARKER### to get the php return there. I used...

return json_encode( $output );

In other posts I saw that "sEcho":0 shouldn't be the case. Is the php script I'm using not compatible with the libraries I'm using?

.../jquery-1.11.1.min.js
.../jqueryui/1/jquery-ui.min.js
.../1.10.5/js/jquery.dataTables.min.js
.../1.10.5/css/jquery.dataTables.css

allan · April 2015

What PHP is rendering that data?

Hmm, I've just taken a look at your initialisation of the table and it looks wrong:

{
    "processing": true,
    "serverSide": true,
    "ajax": {
        "sEcho": 0,
        "iTotalRecords": "6",
        "iTotalDisplayRecords": "6",
        "aaData": [
            ["1", "Hans", "Meier", "51", "2011-04-13", "EUR200"],
            ["2", "Frank", "Heinz", "45", "2004-02-17", "EUR60"],
            ["3", "Katrin", "Kohl", "35", "2011-08-17", "EUR1000"],
            ["4", "Werner", "Pertl", "39", "2013-11-19", "USD499"],
            ["5", "Christina", "Sinn", "22", "2015-03-09", "GBP99"],
            ["6", "Klaus", "Vienna", "67", "1991-01-15", "EUR5000"]
        ]
    }
}

You haven't specified a url for where it should get the data (see ajax).

I don't understand why you have put sEcho, etc, into the ajax object. Could you tell me where you found that in the documentation so I can correct it - it is wrong.

I would suggest reading over the server-side processing manual.

I would also highlight that unless you have 50k+ rows, you are unlikely to need server-side processing.

Allan

Capamania · April 2015

I'm using the php script found here: https://datatables.net/development/server-side/php_mysql Then I embedded the php script in the following structure (required by TYPO3) ...
```
<?php

class custom_datatable {

function custom_table1($output) {   

.... 

return json_encode( $output );

}
}

<?php > ``` ?>

and 'return' the php action to the ###TYPO3 MARKER### in the javascript syntax (which I put/defined next to the 'ajax' call). The thing with the url is, that https://typo3.org/ doesn't allow me to include .php files directly in the html/javascript (eg via a defined path to the php script), due to security. It needs to go indirectly via a defined ###TYPO3 MARKER###. But I can place the marker in html/javascript anywhere I want. The marker then will be replaced by whatever the php returns back. In our case the array.

Reading through the manual, maybe in my case it's an issue with 'ajax'. Since the ###TYPO3 MARKER### maybe only listens to the php script and not to the 'ajax' call.

I really want the server side processing since I have easily 50k+ rows and even more. I'm already using DOM, but it is too slow and the browser freezes.

allan · April 2015

You want your DataTables initialisation to look something like this:

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

Note that the ajax option is a string which points to a URL that can be loaded by Ajax. You can see an example of that here.

I would also suggest using the new style SSP class rather than the legacy script you found above. Click on the "Server-side script" tab below the table in the example I linked to above to see how it is configured.

Key point: If you are using server-side processing, you do not want to embed the results into the page directly. You want to get the results via Ajax.

Allan

Capamania · July 2015

Hi Allan! I now have it running. What I needed to do in TYPO3 is to output the json array via the typNum function in a TYPO3 extension, similar to the post here: http://stackoverflow.com/questions/27925797/typo3-extbase-json-output. And finally define the typNum link (in my case "/index.php?id=45&type=5001" as the source of the json array. So it looks like e.g.

var oTable = $('#datatable02').dataTable({
            "bScrollCollapse": true,
            "sPaginationType": "full_numbers",
            "bAutoWidth": true,
            "bSortCellsTop": true,
            "iDisplayLength": 10,
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": '/index.php?id=45&type=5001',
            "oLanguage": {
                    "sSearch": 'Search all columns:',
                    "sEmptyTable": 'No records found'
            }
        });

allan · July 2015

Excellent - good to hear you got it going. Thanks for posting back with your findings.

Allan

Rendering via 'return' function / server side processing Datatables / Typo3 / PHP

Rendering via 'return' function / server side processing Datatables / Typo3 / PHP

Answers

Howdy, Stranger!

Categories

DataTables

Rendering via 'return' function / server side processing Datatables / Typo3 / PHP

Rendering via 'return' function / server side processing Datatables / Typo3 / PHP

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables