Error when including server-side and ajax processing

Error when including server-side and ajax processing

kathylkathyl Posts: 11Questions: 1Answers: 0

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.

This question has accepted answers - jump to:

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

    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!

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

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

    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.

  • kathylkathyl Posts: 11Questions: 1Answers: 0

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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited September 2021

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

    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"}]}

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

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

    Colin

  • kathylkathyl Posts: 11Questions: 1Answers: 0

    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?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

    { data: "devices.deviceId" },
    ...
    
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

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

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

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

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

    Nothing in the php logs at all!

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

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

    Yes, that is the XHR response!

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

    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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

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

    steppingstonez.com/datatables/index.php

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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

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

    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

  • kathylkathyl Posts: 11Questions: 1Answers: 0

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

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

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

    Colin

Sign In or Register to comment.