Datatable Invalid JSON response error when my JSON is valid? PHP

Question

My data table that is loading its body from another file with ajax is giving me the invalid JSON error, but when I check my developer tools under network responses my JSON is valid?

This is my PHP and SQL:

<?php 
header('Content-Type: application/json');
$output = array('data' => array());
$query = "SELECT * FROM table"; 
$stmt = sqlsrv_query($sapconn2, $query);

$x = 1;

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){

    $output['data'][] = array(
            'col_1' => $x,
            'ID' => $row['ID'],
            'QuoteID' => $row['QuoteID'],
            'CardCode' => $row['CardCode'],
            'SlpCode' => $row['SlpCode'],
            'SlpName' => $row['SlpName'],
            'BandA' => $row['BandA'],
            'NewPrice' => $row['NewPrice']
        );
    $x ++;
}

echo json_encode($output);
?>

This is my JSON thats returned in the browser:

{
"data": [
    [1, 138, 25, "000123", "222", "test data", 222, 222],
    [2, 144, 25, "000123", "132", "test data", 465, 789],
    [3, 160, 25, "000123", "456132", "test data", 5599, 5499],
    [4, 171, 25, "000123", "789", "test data", 7897, 989],
    [5, 172, 25, "000123", "11111", "test data", 1, 11],
    [6, 182, 25, "000123", "132166", "test data", 1323, 133],
    [7, 183, 25, "000123", "135456", "test data", 1332132, 13213],
    [8, 184, 25, "000123", "1321", "test data", 5643214, 6513]
]
}

EDIT:

    var testTable = $("#testTable").DataTable({
    processing: false,
    serverSide: true,
    dataType : 'json',
    ajax: "test.php",
    columns: [
        { "data": "col_1" },
        { "data": "ID" },
        { "data": "QuoteID" },
        { "data": "CardCode" },
        { "data": "SlpCode" },
        { "data": "SlpName" },
        { "data": "BandA" },
        { "data": "NewPrice" }
    ]
    });

DataTables warning: table id=testTable - Invalid JSON response. For more information about this error, please see datatables.net/tn/1 — PHPNewbie
– PHPNewbie, Commented Mar 9, 2017 at 9:38
Please provide AJAX code, set 'dataType':'json' in it. Thanks. — user1544541
– user1544541, Commented Mar 9, 2017 at 9:41
datatables.net/manual/ajax. It seems your json is not what datatable waits for. — Giacomo M
– Giacomo M, Commented Mar 9, 2017 at 9:49

Giacomo M · Accepted Answer · 2017-03-09 10:13:15Z

1

This is what datatable waits for:

{
    "data": [
        {
            "name": "Tiger Nixon",
            "position": "System Architect",
            "salary": "$320,800",
            "start_date": "2011/04/25",
            "office": "Edinburgh",
            "extn": "5421"
        },
        ...
    ]
}

The "data" element is an array of objects, instead you pass an array of array.

You need something like that:

{
    "data": [
        { "id": 1, "second_field": 138, "third_field": 25, "fourth_field": "000123", ... },
        { "id": 2, "second_field": 138, "third_field": 25, "fourth_field": "000123", ... },
    ]
}

EDITED:

$output['data'][] = array(
    'col_1' => $x,
    'col_2' => $row['ID'],
    'col_3' => $row['QuoteID'],
    'col_4' => $row['CardCode'],
    'col_5' => $row['SlpCode'],
    'col_6' => $row['SlpName'],
    'col_7' => $row['BandA'],
    'col_8' => $row['NewPrice']
);

edited Mar 9, 2017 at 10:13

answered Mar 9, 2017 at 9:59

Giacomo M

4,7258 gold badges32 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PHPNewbie Over a year ago

Ahh, thanks! Where would I define the fields into this format?

PHPNewbie Over a year ago

Okay, made them changes, my JSON data is now coming back like you said I would need, and it validates, but still, I get the same error when loading the table?

Giacomo M Over a year ago

Did you add the "columns" attribute to the datatable definition?

PHPNewbie Over a year ago

Yes I did, I will edit my question to show you what i have done.

annoyingmouse · Accepted Answer · 2017-03-09 10:28:43Z

1

When you make a request to a server-side script from DataTables with processing set to true then it sends this data.

When it returns data DataTables expects the data to follow these conventions.

You can either take these into account with your server-side script (there's a good example here.) or choose a different method for adding your data. If you perhaps set processing to false you might find everything just works as you expect.

Hope that helps.

answered Mar 9, 2017 at 10:28

annoyingmouse

5,6991 gold badge31 silver badges48 bronze badges

4 Comments

PHPNewbie Over a year ago

Thanks for taking the time to comment, I have tried it set to false, but unfortunately, I still get the same Invalid JSON error

annoyingmouse Over a year ago

Hey @PHPNewbie, another issue is that when you initialize your table you are telling it to expect an array of objects whereas you're just giving it an array or arrays. The other answer gives you details of the type of data you need to return. In effect, you've got two problems at the same time: asking your server for information that it has no hope of understanding and when it does the best it can, it returns data in the wrong format. Hope taht makes sense? DataTables are built for this sort of thing but you need to clock the right way to implement them.

PHPNewbie Over a year ago

Thanks! Could you please give me a hand in showing me how to make this work? It would be GREATLY appriciated

PHPNewbie Over a year ago

Fixed it finally, i was including HTML in the document which was making it not validate. Thanks again.

Collectives™ on Stack Overflow

Datatable Invalid JSON response error when my JSON is valid? PHP

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related