Error when including server-side and ajax processing

kathyl

Here is a link to my test code:

https://jsfiddle.net/ktpmm5/drogpca3/10/

As soon as I add the js code:
"serverSide": true, ajax: "includes/server_processing.php"

I get the error message:
table id=scandata - Requested unknown parameter '0' for row 0, column 0.

The relevant portions of server_processing are:

$table = 'devices';
$primaryKey = 'deviceId';
$columns = array(
    array( 'db' => 'deviceId', 'dt' => 'deviceId' ),
    array( 'db' => 'hostname_id',  'dt' => 'hostname_id' ),
    array( 'db' => 'ip_id',   'dt' => 'ip_id' ),
    array( 'db' => 'macaddr_id',     'dt' => 'macaddr_id' ),
    array( 'db' => 'osFamily_id',     'dt' => 'osFamily_id' ),
    array( 'db' => 'network_id',     'dt' => 'network_id' ),
    array( 'db' => 'status',     'dt' => 'status' )
);

My db table devices consists of the following:
deviceId - auto
hostname_id
ip_id
macaddr_id
osFamily_id
network_id
status

What am I missing here when I try to grab the data via server_processing? I am connecting fine to the db and there are no js or html errors.

tangerine

Eight HTML columns, seven DT columns. They should match.

kathyl

Oh boy, that was a big miss by me. Thanks for the catch.

I think I left it out because I wasn't sure how to do a left join in server_processing.php - how do you do a more complicated sql query? Or should I ask this in a separate question?

Thank you!

colin

What kind of query did you have in mind? It's normally recommended to use the Editor library for complex server-side queries - see blog post here explaining it.

Colin

kathyl

Here is my query:

    SELECT deviceId, h.hostname, m.macAddress, i.ip, s.openPorts, n.network from devices d
      LEFT JOIN hostname h ON (d.hostname_id = h.hostnameId )
      LEFT JOIN ipAddr i ON (d.ip_id = h.ipId)
      LEFT JOIN macAddr m ON (d.macAddr_id = m.macId)
      LEFT JOIN nmapScans s ON (d.deviceId = s.deviceId)
      LEFT JOIN network n  ON (d.networkId = s.deviceID)

The query works but I wasn't sure how to use it in server_processing. I'll look at the Editor. Thanks.

kathyl

@tangerine - I updated my html to only 7 columns, but I get the same error. (Didn't update jsfiddle since I'm at a diff location today).

The correct json is returned (I can see it in Firefox's Web Developer XHR Response) but it still does not show.

kthorngren

Use the browser's developer tools to see the actual JSON response. Sounds like the row data is not where Datatables expects it in the response or your are using objects but Datatables is expecting arrays. If you still need help please post the JSON response here.

Kevin

kathyl

Here is the response - I am getting objects back:

{"draw":1,"recordsTotal":3,"recordsFiltered":3,"data":[{"deviceId":"1","hostname":"localhost","ip":"33686018","macAddress":"08:00:27:01:74:8c","network":"test","osFamily":"Linux","status":"up"},{"deviceId":"2","hostname":"scanme.org","ip":"168430090","macAddress":"08:00:27:01:74:05","network":"test","osFamily":"Linux","status":"up"},{"deviceId":"3","hostname":"me.test.com","ip":"202216108","macAddress":"08:00:01:01:74:8c","network":"test","osFamily":"Linux","status":"up"}]}

colin

That looks OK, but you would need to define those columns in the DataTables initialisation - see example here,

Colin

kathyl

Makes sense, or so I thought.

Here are my columns defined:

    columns: [
      { data: "deviceId" },
      { data: "hostname" },
      { data: "ipAddr" },
      { data: "macAddr" },
      { data: "network" },
      { data: "osFamily" },
      { data: "status" },
    ]

Here is my exampleController.php:

Editor::inst( $db, 'scandata' )
    ->field(
        Field::inst( 'devices.deviceId' ),
        Field::inst( 'hostname.hostname' ),
        Field::inst( 'ipAddr.ipAddr' ),
        Field::inst( 'macAddr.macAddress' ),
        Field::inst( 'network.network' ),
        Field::inst( 'osFamily.osFamily' ),
        Field::inst( 'devices.status' )
    )
    ->leftJoin( 'hostname', 'hostname.hostnameId', '=', 'devices.hostname_id ),
    ->leftJoin( 'ipAddr',  'ipAddr.ipId', '=', 'devices.ip_id' ),
    ->leftJoin( 'macAddress', 'macAddr.macId', '=', 'devices.macAddr_id'  ),
    ->leftJoin( 'network', 'network.networkId', '=', 'devices.network_id' ),
    ->leftJoin( 'osFamily', 'osFamily.osFamilyId', '=', 'devices.osFamily_id' ),
    ->write( false )
    ->process( $_POST )
    ->json();

Which represents this query:

SELECT d.deviceId, h.hostname, i.ip, m.macAddress, n.network, o.osFamily, d.status from `devices` d
LEFT JOIN hostname h ON ( d.hostname_id = h.hostnameId )
LEFT JOIN ipAddr i ON ( d.ip_id = i.ipId )
LEFT JOIN macAddress m ON ( d.macAddr_id = m.macId )
LEFT JOIN network n ON ( d.network_id = n.networkId )
LEFT JOIN osFamily o  ON ( d.osFamily_id = o.osFamilyId )

This is my request:
{"draw":"1","columns[0][data]":"devices.deviceId","columns[0][name]":"","columns[0][searchable]":"true","columns[0][orderable]":"true","columns[0][search][value]":"","columns[0][search][regex]":"false","columns[1][data]":"hostname.hostname","columns[1][name]":"","columns[1][searchable]":"true","columns[1][orderable]":"true","columns[1][search][value]":"","columns[1][search][regex]":"false","columns[2][data]":"ipAddr.ipAddr","columns[2][name]":"","columns[2][searchable]":"true","columns[2][orderable]":"true","columns[2][search][value]":"","columns[2][search][regex]":"false","columns[3][data]":"macAddr.macAddr","columns[3][name]":"","columns[3][searchable]":"true","columns[3][orderable]":"true","columns[3][search][value]":"","columns[3][search][regex]":"false","columns[4][data]":"network.network","columns[4][name]":"","columns[4][searchable]":"true","columns[4][orderable]":"true","columns[4][search][value]":"","columns[4][search][regex]":"false","columns[5][data]":"osFamily.osFamily","columns[5][name]":"","columns[5][searchable]":"true","columns[5][orderable]":"true","columns[5][search][value]":"","columns[5][search][regex]":"false","columns[6][data]":"devices.status","columns[6][name]":"","columns[6][searchable]":"true","columns[6][orderable]":"true","columns[6][search][value]":"","columns[6][search][regex]":"false","order[0][column]":"0","order[0][dir]":"asc","start":"0","length":"10","search[value]":"","search[regex]":"false"}

Yes I get invalid JSON. The scan above works from the mysql command line, so I know it has to be a datatables problem.

All of this seems to be what datatables is looking for. What am I missing?

tangerine

Have you tried including table names in your js "columns" definitions?

{ data: "devices.deviceId" },
...

kthorngren

After the changes you made you are now getting the Invalid JSON Response error?

What is the XHR response you are now getting? Please past the response found in the browsers network inspector.

Kevin

kathyl

@tangerine - yes, I've tried include table names - it doesn't change anything.
@kthorngren, yes, I am getting invalid Json error. The Response Payload is odd:

include("DataTables.php");
use
    DataTables\Editor,
    DataTables\Editor\Field;


Editor::inst( $db, 'scandata' )
    ->field(
        Field::inst( 'devices.deviceId' ),
        Field::inst( 'hostname.hostname' ),
        Field::inst( 'ipAddr.ipAddr' ),
        Field::inst( 'macAddr.macAddr' ),
        Field::inst( 'network.network' ),
        Field::inst( 'osFamily.osFamily' ),
        Field::inst( 'devices.status' )
    )
    ->leftJoin( 'hostname.hostname', 'hostname.hostnameId', '=', 'devices.hostname_id ),
    ->leftJoin( 'ipAddr.ipAddr',  'ipAddr.ipId', '=', 'devices.ip_id' ),
    ->leftJoin( 'macAddr.macAddr', 'macAddr.macId', '=', 'devices.macAddr_id'  ),
    ->leftJoin( 'network.network', 'network.networkId', '=', 'devices.network_id' ),
    ->leftJoin( 'osFamily.osFamily', 'osFamily.osFamilyId', '=', 'devices.osFamily_id' ),
    ->write( false )
    ->process( $_POST )
    ->json();

Please note: I typed incorrectly above, and macAddress should have been macAddr. Still did not help.

kthorngren

The Response Payload is odd:

What is in the response?

Have you looked at your server logs to see if there is anything useful?

Sorry I don't use PHP but maybe the steps to enable debugging, from this docs will provide useful information. The developers can help you further with the PHP server scripts.

Kevin

kathyl

@kthorngren - the response payload is above and is the request.

Nothing in the php logs at all!

kthorngren

You are saying that this is the XHR response?

include("DataTables.php");
use
    DataTables\Editor,
    DataTables\Editor\Field;
 
 
Editor::inst( $db, 'scandata' )
    ->field(
        Field::inst( 'devices.deviceId' ),
        Field::inst( 'hostname.hostname' ),
        Field::inst( 'ipAddr.ipAddr' ),
        Field::inst( 'macAddr.macAddr' ),
        Field::inst( 'network.network' ),
        Field::inst( 'osFamily.osFamily' ),
        Field::inst( 'devices.status' )
    )
    ->leftJoin( 'hostname.hostname', 'hostname.hostnameId', '=', 'devices.hostname_id ),
    ->leftJoin( 'ipAddr.ipAddr',  'ipAddr.ipId', '=', 'devices.ip_id' ),
    ->leftJoin( 'macAddr.macAddr', 'macAddr.macId', '=', 'devices.macAddr_id'  ),
    ->leftJoin( 'network.network', 'network.networkId', '=', 'devices.network_id' ),
    ->leftJoin( 'osFamily.osFamily', 'osFamily.osFamilyId', '=', 'devices.osFamily_id' ),
    ->write( false )
    ->process( $_POST )
    ->json();

If so then you will need to look at the server to determine why its returning the script instead of running the script.

Kevin

kathyl

Yes, that is the XHR response!

colin

As Kevin said, you shouldn't be getting the script, the script should be running and sending data! Are you able to link to your page so we can debug?

Colin

allan

That you are getting the PHP code suggests to me that your web-server is not set up correctly. You need to have it execute the PHP, which will be done by a handler in your HTTP server.

You should be able to have:

<?php
echo 'Hello world';

In a script (say hello.php) and then load http://mysite/hello.php and have it display just Hello world.

Allan

kathyl

My web server is set up normally and my index.php shows up correctly. Here is the page:

steppingstonez.com/datatables/index.php

kthorngren

I'm not familiar with PHP but don't you need <?php` at the beginning of the PHP script and `?> at the end? Doesn't look like those exist in the response.

Kevin

colin

As Kevin said, so it means the script isn't being executed, it's just being returned. See Allan's example above for the format of the PHP script.

Colin

kathyl

Well, don't I feel stupid. And I should know better - my apologies for wasting your time on this. Thanks for all the responses.

colin

We've all done it before, and I'm sure we'll all do it again

Colin