DataTables warning: table id=example - Invalid JSON response.

rbastida

I am using DataTables 1.10.21 with Server Side option and 4000 record´s MySQL database. In my localhost environment works fine, but when I try the same in the production server, it fails with the message:

"DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1"

I tried to inspect the site with Firefox, but there´s no output of the JSON data, even when I execute server side script directly.....There´s no PHP error logged.

I also have tried DataTable´s debug option - https://debug.datatables.net/ezafed

Anybody can help me?

server-side-script.php

$table = 'customers_filtrado';

$primaryKey = 'id_houses';

$columns = array(

    array( 
        'db'        => 'firstname',
        'dt'        => 0,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT 
            firstname
            FROM
            customers
            WHERE
            id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $firstname = $rs['firstname'];

            return "<a href='comments.php?id=".$row['id_houses']."'>".$d."</a>";

        }),       

    array( 'db' => 'lastname',          'dt' => 1 ),
    array( 'db' => 'phone',             'dt' => 2 ),
    array( 'db' => 'email',             'dt' => 3 ),
    array( 'db' => 'city',              'dt' => 4 ),
    array( 'db' => 'address',           'dt' => 5 ),
    array( 'db' => 'state',             'dt' => 6 ),
    array( 'db' => 'zipcode',           'dt' => 7 ),
    array( 'db' => 'bedroom',           'dt' => 8 ),
    array( 'db' => 'bathroom',          'dt' => 9 ),
    array( 'db' => 'square_footage',    'dt' => 10 ),
    array( 'db' => 'basement',          'dt' => 11 ),
    array( 'db' => 'sewer',             'dt' => 12 ),
    array( 'db' => 'situation',         'dt' => 13 ),
    array( 'db' => 'sell_keep',         'dt' => 14 ),
    array( 'db' => 'date_ymd_hs',       'dt' => 15 ), 
    array( 'db' => 'id_houses',         'dt' => 16 )

);                       

// SQL server connection information
$sql_details = array(
    'user' =>   Appconf::DBUSER,
    'pass' =>   Appconf::DBPASSWORD,
    'db'   =>   Appconf::DBNAME,
    'host' =>   Appconf::DBHOST
); 

require( 'ssp.class.php' );

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

report.html

         $(document).ready(function () {
            $('#example').DataTable({
                    "processing": true,
                    "serverSide": true,
                    ajax: {
                        url: "/../../admin/server-side-data.php",
                        type: "GET"
                    }
                });
            });

colin

but there´s no output of the JSON data

This is likely the issue - it sounds like no JSON is being returned, which is invalid JSON. Are you able to link to the page so we can see?

Colin

rbastida

This is the page...
https://www.estateservicing.com/admin/report_customer_list_view.php

tangerine

You have seventeen columns in your $columns array, but sixteen in your HTML.

EDIT: Not sure that's the primary issue. Take a look at the "network" response as explained in the link http://datatables.net/tn/1.

rbastida

No, it´s not the issue. My network response is empty.
https://prnt.sc/smxcfp

colin

Yep, agreed, the server script is returning nothing. so you'll need to debug that. Is it connecting to the database correctly? Is it getting the correct values?

Colin

rbastida

The DB connection works fine and getting the correct values at my local environment, so I supposed it´s not the issue. I generated a debug report and change $_GET to $_POST...

https://debug.datatables.net/agocuc

kthorngren

It looks like your example ( https://www.estateservicing.com/admin/report_customer_list_view.php ) is working now? Do you still have a problem to debug?

Kevin

rbastida

I have changed the code. On the report.html and server-side-data.php stayed like this:

report.html

            $(document).ready(function () {
                $('#example').DataTable({
                    "processing": true,
                    "serverSide": true,
                    "columnDefs": [{
                            // The `data` parameter refers to the data for the cell (defined by the
                            // `data` option, which defaults to the column being worked with, in
                            // this case `data: 0`.
                            "render": function (data, type, row) {
                                return '<a href="comments.php?id=' + row[0] + '">' + row[1] + '</a>';
                            },
                            "targets": 1
                        },
                        {"visible": false, "targets": [0]}
                    ],
                    ajax: {
                        url: "server-side-data.php",
                        type: "POST"
                    }

                });
            });

server-side-data.php

<?php
require_once(dirname(__FILE__) . '/../inc/config/AppConf.php');
require_once(dirname(__FILE__) . '/../inc/DBConnection.php');

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

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

$columns = array(

    array( 'db' => 'id_houses',         'dt' => 0 ),
    array( 'db' => 'firstname',         'dt' => 1 ),
    array( 'db' => 'lastname',          'dt' => 2 ),
    array( 'db' => 'phone',             'dt' => 3 ),
    array( 'db' => 'email',             'dt' => 4 ),
    array( 'db' => 'city',              'dt' => 5 ),
    array( 'db' => 'address',           'dt' => 6 ),    
    array( 'db' => 'state',             'dt' => 7 ),
    array( 'db' => 'zipcode',           'dt' => 8 ),

    array( 
        'db'        => 'bedroom',
        'dt'        => 9,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            b.nm_house_bedroom_red
            FROM
            house_bedroom b
            INNER JOIN
            customers c ON b.id_house_bedroom = c.bedroom 
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_bedroom_red = $rs['nm_house_bedroom_red'];    
            return $nm_house_bedroom_red;

        }),       

    array( 
        'db'        => 'bathroom',
        'dt'        => 10,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            b.nm_house_bathroom_red
            FROM
            house_bathroom b
            INNER JOIN
            customers c ON b.id_house_bathroom = c.bathroom 
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_bathroom_red = $rs['nm_house_bathroom_red'];    
            return $nm_house_bathroom_red;

        }),       

    array( 
        'db'        => 'square_footage',
        'dt'        => 11,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            s.nm_house_square_footage_red
            FROM
            house_square_footage s
            INNER JOIN
            customers c ON s.id_house_square_footage = c.square_footage 
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_square_footage_red = $rs['nm_house_square_footage_red'];    
            return $nm_house_square_footage_red;

        }),       

    array( 
        'db'        => 'basement',
        'dt'        => 12,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            bs.nm_house_basement_red
            FROM
            house_basement bs
            INNER JOIN
            customers c ON bs.id_house_basement = c.basement
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_basement_red = $rs['nm_house_basement_red'];    
            return $nm_house_basement_red;

        }),       

    array( 
        'db'        => 'sewer',
        'dt'        => 13,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            s.nm_house_sewer_red
            FROM
            house_sewer s
            INNER JOIN
            customers c ON s.id_house_sewer = c.sewer
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_sewer_red = $rs['nm_house_sewer_red'];    
            return $nm_house_sewer_red;

        }),       

    array( 
        'db'        => 'situation',
        'dt'        => 14,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            s.nm_house_current_situation_red
            FROM
            house_current_situation s
            INNER JOIN
            customers c ON s.id_house_current_situation = c.situation
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_current_situation_red = $rs['nm_house_current_situation_red'];    
            return $nm_house_current_situation_red;

        }),       

    array( 
        'db'        => 'sell_keep',
        'dt'        => 15,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            s.nm_house_sell_keep_red
            FROM
            house_sell_keep s
            INNER JOIN
            customers c ON s.id_house_sell_keep = c.sell_keep
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $nm_house_sell_keep_red = $rs['nm_house_sell_keep_red'];    
            return $nm_house_sell_keep_red;

        }),       

    array( 
        'db'        => 'date_ymd_hs',
        'dt'        => 16,
        'formatter' => function( $d, $row ) {

            $sql  = "
            SELECT
            DATE_FORMAT(date_ymd_hs, '%Y-%m-%d') as register          
            FROM
            customers c
            WHERE
            c.id_houses = :id_houses";        
            $stmt = DB::prepare($sql); 
            $stmt->bindParam(':id_houses',                $row['id_houses'],   PDO::PARAM_INT);
            $stmt->execute();    
            $rs = $stmt->fetch(PDO::FETCH_ASSOC);    
            $register = $rs['register'];    
            return $register;

        })             

);

// SQL server connection information
$sql_details = array(
    'user' =>   Appconf::DBUSER,
    'pass' =>   Appconf::DBPASSWORD,
    'db'   =>   Appconf::DBNAME,
    'host' =>   Appconf::DBHOST
); 
require( 'ssp.class.php' );

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

I believe that what was causing overhead was calling a view "customers_filtrado" instead of "customers" table. directly But I believe that the way I coded this part is not so much efficient ... Is there a better way to work with "INNER JOINS" ?? What is better, do the data transformation on the client side or on the server side, as I did in the id_houses column?