Server-side processing, empty JSON

antoineb59 · January 2017

Hi,

For a first try I would like to display only the ids of a table in a Datatable by using Serverside processing. But I'm searching for a few days and I can't success. I get a 'invalid json response' What's wrong with that :

<!DOCTYPE html>
<html>
    <head> 
        <script type="text/javascript">
            var table;
            $(document).ready(function() {
                table = $('#table').DataTable({ 
                    "processing": true, 
                    "serverSide": true, 
                    "ajax": {
                        "url": "application/php/joinArray.php",
                        "type": "POST"
                    } ,
                "columns": [
                    { "data": "id" }]
                });
            });
        </script>
    </head> 
<body>
    <div class="container">
        <table id="table" class="table table-striped table-bordered" cellspacing="0" width="100%">
            <thead>
                <tr>
                     <th>Id</th>
                </tr>
            </thead>
            <tfoot>
            <tr>
                     <th>Id</th>
            </tr>
            </tfoot>
        </table>
    </div>
</body>
</html>```


Server side phpscript :

<?php
include('../libraries/Editor-PHP-1.6.1/php/DataTables.php');
use
    DataTables\Editor,
    DataTables\Editor\Field,
 
Editor::inst( $db, 'interventions' )->fields(Field::inst('id'))->process($_POST)->json();

allan · January 2017

invalid json response

We'd need to know what the server is returning since it isn't valid JSON. Likely it will contain some information about an error.

The error message links to this tech note which explains how you want find out what the server is returning.

Allan

antoineb59 · January 2017

There is no response in the response tab of the firefox developpement tools.

allan · January 2017

An empty response is invalid JSON, so the error message is correct. The question therefore is, why is there nothing being returned?

Can you check the server's error logs please? Hopefully there will be something of use in there.

Allan

kaluosi · February 2017

I have the same or similar problem. I have replicated the server-side examples with my own database information. I have only changed the database and table information, the table structure in html and the $columns array in server_processing.php script. In a first try of the simple.html example json returns nothing (There is no response in the response tab of the firefox development tools). The strange thing is that if you click two times on a column header (as when you want to change the order of that column), then every thing appears and the tables works when you click to see more records (other pages). Then, suddenly the table stop to show more pages and a "Processing..." message appears. Checking then the json response it is empty again.
My database is simple but have 25.000 registers. I guess the php scripts are not working properly and the server_processing script is returning an empty json response when it should not.

allan · February 2017

I'd suggest taking a look at your server's error logs in the first instance. That might show something useful.

Allan

kaluosi · February 2017

After deleting de httpd logs error.log and access.log and restarting apache these are the content of the files:

error.log
Fri Feb 10 18:40:48.609332 2017 mpm_prefork:notice pid 20748 AH00163: Apache/2.4.18 (Ubuntu) configured -- resuming normal operations
Fri Feb 10 18:40:48.609401 2017 core:notice pid 20748 AH00094: Command line: '/usr/sbin/apache2'

access.log
127.0.0.1 - - 10/Feb/2017:18:41:30 +0100 "GET /scrud-regla/datatables/examples/server_side_regla/scripts/127.0.0.1 - - 10/Feb/2017:18:41:30 +0100 "GET /scrud-regla/datatables/examples/server_side_regla/scripts/server_processing.php?draw=1&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=6&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1486748490888 HTTP/1.1" 200 203 "http://localhost/scrud-regla/datatables/examples/server_side_regla/simple.html" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:51.0) Gecko/20100101 Firefox/51.0"

Nothing seems wrong in the httpd server. The only thing is that server_processing.php returns an empty json response. I bet that the problem is in the php script.

kaluosi · February 2017

At last, in my case the problem was related with the charset specification in the database connexion. I found the error using json_last_error() or json_last_error_msg() function. I use the code in php.net/manual/en/function.json-last-error-msg.php.
With this I found there was a malformed UTF-8 characters.
Then I remember that many times you have to specify the charset in the database connexion when it is not the standard.
To fix this I add charset to $sql_details:

$sql_details = array(
    'user' => 'username',
    'pass' => 'password',
    'db'   => 'database_name',
    'host' => 'localhost',
    'charset' => 'utf8'
);

then I changed the connexion function in ssp.class.php like this:

static function sql_connect($sql_details)
    {
        try {
            $db = @new PDO(
                "mysql:host={$sql_details['host']};dbname={$sql_details['db']};charset={$sql_details['charset']}",
                $sql_details['user'],
                $sql_details['pass'],
                array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                       PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')
            );
        } catch (PDOException $e) {
            self::fatal(
                "An error occurred while connecting to the database. " .
                "The error reported by the server was: " . $e->getMessage()
            );
        }

        return $db;
    }

After these changes the json response is right the first time.

allan · February 2017

Awesome. Thanks for posting back. Good to hear you've got it working now.

Allan

LoveCode · April 2017

Thanks kaluosi for the solution, now my Datatables work well too.
Please Allan, add the "charset" in sql_details in next version.

pixa · August 2017

Big thanks Kaluosi, i've been troubleshooting this for las two days.

RobeLTDP · May 2018

I have only registered to thank you, Kaluosi! Great job, works like a charm! :-)

monkeyboy · June 2018

I am using a custom php for a complex query which cannot be supported by the library.
If I get no returned row from my SQL query, what do I want to send back as a json string, so that
"language": {
"emptyTable": "No matching rows found"
},
will function?
I tried this on the php side:
$emptyArray = array();
if (mysqli_num_rows($findMatchingResult) == 0)
{
echo json_encode($emptyArray);
exit();
}

It just continues to show a message of: "Loading..."

allan · June 2018

Can you use the debugger when that happens and let me know the debug trace.

Thanks,
Allan

monkeyboy · June 2018

https://debug.datatables.net/equleh

Never used the debugger before. Neat trick!
Still doing woodwork?

monkeyboy · June 2018

Unable to update the datatables libraries at this time due to release control rules. If this is due to a known bug, I can request a server move to an uncontrolled machine

allan · June 2018

Still doing woodwork?

As often as possible, which isn't very often at the moment!

It looks like the issue is that you are currently returning just [], but DataTables is expecting the data inside a data array - e.g. {"data":[]}. If your table has data, does it use the data array?

echo json_encode(["data"=>[]]); will do if that is the case.

Allan

Server-side processing, empty JSON

Server-side processing, empty JSON

Answers

Howdy, Stranger!

Categories

DataTables

Server-side processing, empty JSON

Server-side processing, empty JSON

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables