Server-side processing, empty JSON

Server-side processing, empty JSON

antoineb59antoineb59 Posts: 2Questions: 1Answers: 0

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

Answers

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

    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

  • antoineb59antoineb59 Posts: 2Questions: 1Answers: 0

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

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

    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

  • kaluosikaluosi Posts: 19Questions: 6Answers: 0

    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.

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

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

    Allan

  • kaluosikaluosi Posts: 19Questions: 6Answers: 0
    edited 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.

  • kaluosikaluosi Posts: 19Questions: 6Answers: 0
    edited 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.

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

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

    Allan

  • LoveCodeLoveCode Posts: 7Questions: 1Answers: 0

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

  • pixapixa Posts: 1Questions: 0Answers: 0

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

  • RobeLTDPRobeLTDP Posts: 1Questions: 0Answers: 0

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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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..."

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

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

    Thanks,
    Allan

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    https://debug.datatables.net/equleh

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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

    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

This discussion has been closed.