DataTables warning: table id=DataTables_Table_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Question

I tried server processing for jQuery data table but getting error:

DataTables warning: table id=DataTables_Table_0 - Ajax error

and when I remove below code from server.php

require( '/machines/jquery/ssp.class.php' );

echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )
);

then getting error:

DataTables warning: table id=DataTables_Table_0 - Invalid JSON response

Also note that, previously require was: require( 'ssp.class.php' ); as per https://datatables.net/examples/server_side/post.html but I tried to find ssp.class.php in entire / directory in my ubuntu OS but there was no such php file so searched google and copied that file and kept under /machines/jquery/ssp.class.php but still no luck.

I have already checked all the php, pdp .. settings and it is correct so something is wrong in the code or my server doesn't understand the way jQuery given the server processing example.

There is no problem for client side data processing but server side data processing gives above error.

 root@m100:~# php -i|grep PDO
 PDO
 PDO support => enabled
 PDO drivers => mysql
 PDO Driver for MySQL => enabled

client.php:

<div>

<h3>Machines Data</h3>
<div class="container">
    <div id="machines-data" style="padding-left: 15px; padding-right:  15px;"></div>

    <table class="table machines-data" border="1">

    </table>

</div>

</div>


<script>

$(document).ready(function () {

});

<script>
 $(function(){
    $("#progress").show();

        $(".machines-data").dataTable({
            "processing": true,
            "serverSide": true,
            "ajax": { "url": "/machines/server.php", "type": "POST" },
            "columns": [
        { "data": "id" },
        { "data": "ip" },
        { "data": "machine" },
        { "data": "action" },
    ]

        });
  })
 </script>

server.php:

<?php

ignore_user_abort(true);

if($_POST) {

$table = '1_machine_data';

$primaryKey = 'id';

 $columns = array(
     array( 'db' => 'id', 'dt' => 'id' ),
     array( 'db' => 'ip',  'dt' => 'ip' ),
     array( 'db' => 'machine',   'dt' => 'machine' ),
     array( 'db' => 'action',     'dt' => 'action' ),
 );

$sql_details = array(
    'user' => 'root',
    'pass' => 'pass',
    'db'   => 'machine',
    'host' => 'localhost'
);

require( '/machines/jquery/ssp.class.php' );

echo json_encode(
     SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )
);

exit (0);

}

?>

Solution 1

Here is the answer! I used php+datatable server processing code, instead of SSP stuff..

<?php

ignore_user_abort(true);

if($_POST) {

    $aColumns = array( 'id', 'ip', 'machine', 'status' );

    $sIndexColumn = "id";

    $sTable = "1_machines";

    $gaSql['user']       = "root";
    $gaSql['password']   = "pwd";
    $gaSql['db']         = "machines";
    $gaSql['server']     = "localhost";

    function fatal_error ( $sErrorMessage = '' )
    {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }

    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 ' );
    }

    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
    {
        $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
            intval( $_GET['iDisplayLength'] );
    }

    $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 = "";
        }
    }

    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

    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])."%' ";
        }
    }


    $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() );

    $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];

    $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 = 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] == "started_on" )
            {
                $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 );

    exit (0);

}

?>

Solution 2

For whatever it may be worth. I had the same problem. It happened because I deployed two shiny applications in Windows 10. I needed to completely force-close RStudio from task manager before executing the second app. After this, the DT table load well.