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

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

rbastidarbastida Posts: 5Questions: 1Answers: 0

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

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    edited May 2020

    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.

  • rbastidarbastida Posts: 5Questions: 1Answers: 0

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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • rbastidarbastida Posts: 5Questions: 1Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

    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

  • rbastidarbastida Posts: 5Questions: 1Answers: 0

    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?

This discussion has been closed.