Server-Side Processing broke my ordering and conditional formatting

fitzpr

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

Description of problem: I'm using the standard ssp.class.php and server_processing.php files to great success on my 1.2 million records in mysql. However since the functionality shifted to the server and away from my simplistic client-side php, the ordering of each column in the ui/table produces an error like above. I'm sure I need to make some small changes in the aforementioned php files on the server but i'm a bit new to it.

My guess is that some of the responses are blank when XHR'ing upon pressing the ordering/sorting button at the top of the columns. How do I fix or perhaps handle this better?

Thanks!!

fitzpr

kthorngren

The place to start is by following the troubleshooting steps provided in the error's link:
http://datatables.net/tn/1

What is the response you are getting? You might need to look at the server's log to see if there are errors. Let us know what you find.

Kevin

fitzpr

I had followed those steps and can see the ordering responds with nothing when i click some of the columns hence the error? The first and last columns which have data in EVERY row never produce this error when I click to order my them. Im guessing this is key to the issue. a blank column produces an empty response instead of an array of NULL?

Thanks

kthorngren

I'm not familiar with the PHP scripts supplied by Datatables. They will need to be debugged to find out the problem. You can post script with the DB query here to see if anything obvious is wrong. You might need to post a link to your page for the developers to take a look. You can PM Allan or Colin with a link if you don't want to make it public.

Kevin

fitzpr

Thanks Kevin, appreciate it. The real engine of the server-side stuff I'm using is here [https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php]
https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/server_processing.php

Theres an ordering function but i dont full understand it.

allan

At the top of your server_processing.php file (inside the <?php though) can you add:

error_reporting(E_ALL);
ini_set('display_errors', '1');

That will show any error messages in the response from the server. If you could let us know what that shows please.

Also, can you show us both:

• Your DataTables initialisation code
• The PHP server code (from server_processing.php).

Thanks,
Allan

fitzpr

The two additional lines didnt produce any new or verbose errors in the console/devtools.

Init code

$(document).ready(function() { $('#domains').DataTable( { "pagingType": "full_numbers", "pageLength": 25, "order": [[ 10, 'desc']], "processing": true, "ordering": true, "serverSide": true, "ajax": { "url": "scripts/server_processing.php", "dataType": "json", "contentType": "application/json; charset=utf-8" }, } ); } );

Server_processing

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

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

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

// Array of database columns which should be read and sent back to DataTables.
// The db parameter represents the column name in the database, while the dt
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'UniqueID', 'dt' => 0),
array( 'db' => 'URI', 'dt' => 1 ),
array( 'db' => 'Title', 'dt' => 2 ),
array( 'db' => 'R', 'dt' => 3 ),
array( 'db' => 'CVE', 'dt' => 4 ),
array( 'db' => 'js', 'dt' => 5 ),
array( 'db' => 'Ports', 'dt' => 6 ),
array( 'db' => 'fs', 'dt' => 7 ),
array( 'db' => 'Panels', 'dt' => 8 ),
array( 'db' => 'SDT', 'dt' => 9 ),
array( 'db' => 'Date', 'dt' => 10 )
);

// SQL server connection information
$sql_details = array(
'user' => 'myusername',
'pass' => 'secret!',
'db' => 'mysql',
'host' => '127.0.0.1'
);

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/

require( 'ssp.class.php' );

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

fitzpr

It seems maybe? an issue with switching columns 2-9 between DSC and ASC ordering. The columns that have EVERY row populated have no issues switching between ordering. Whereas 2-9 have plenty of NULL rows. Just a thought.

kthorngren

That will show any error messages in the response from the server.

I think Allan wants you to use the browser's network inspector tool to vie the XHR response.

Kevin

fitzpr

Thanks @kthorngren

If i take a "good" xhr request (very long GET with all the columns/index numbers, I get a json response. Then when i hit any column header (changing the GET request to ASC/DESC... it pops the error and the response is blank. See the images i shared at the start. Nothing has changed after adding those lines to show errors. Sorry to go around in circles. I'm not a developer!

colin

Yep, but we need to see the message being sent by the server to the browser. If you press F12 on the brower and go to the network page, you'll see the traffic. Can you post the response from the server at that point. Or, if you can link to your page, we can take a look,

Colin

fitzpr

Hi Colin i sent you a message directly thanks