Invalid JSON response when searching data on datatables

michaeljulyus

I'm using Ignited Datatables in Codeigniter to show data from database. It works fine and the data shows normally. But when I'm searching specific data in datatables (using datatables search box), it shows me an error like this:

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

Controller:

public function json(){
    header('Content-Type: application/json');
    echo $this->m_kategori->json();
}

Model:

function json() {
    $this->datatables->select('*');
    $this->datatables->from('t_kategori');
    return $this->datatables->generate();
}

View:

<table id="table" class="table table-striped table-bordered" width="100%">
    <thead>
        <tr>
            <th width="5%">No</th>
            <th>Kode</th>
            <th>Kategori Pangan</th>
            <th width="20%"></th>
        </tr>
    </thead>
</table>

Javascript:

table = $('#table').DataTable({
        processing: true, //Feature control the processing indicator.
        serverSide: true, //Feature control DataTables' server-side processing mode.
        order: [[1, 'desc']], //Initial order.
        // Load data for the table's content from an Ajax source
        ajax: {
            url: "kategori/json",
            type: "POST"
        },
        //Set column definition initialisation properties.
        columns: [
            {
                data: null,
                orderable: false,
                render: function (data, type, row, meta) {
                    return meta.row + meta.settings._iDisplayStart + 1;
                }
            },
            {data: 'id_kategori'},
            {data: 'nama_kategori'},
            {
                className: 'center',
                orderable: false,
                defaultContent: '<button class="btn btn-warning fa fa-edit" data-toggle="modal" data-target="#editModal"> Ubah</button> <emsp/> <button name="delete" class="btn btn-danger fa fa-trash"> Hapus</button>'
            }
        ]
    });

Debug:

What's wrong here?

colin

Hi @michaeljulyus ,

Have you followed the diagnostic steps in the technical note linked to in the error? That's the best place to start. If so, what was the outcome?

If still no help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

michaeljulyus

@colin Yes, I have followed the diagnostic steps in the technical note linked to in the error. When I'm searching specific data in datatables, it's not returning the json (invalid json). But, when I directly visit the kategori/json url from the browser, it prints the json result. What's wrong here?

kthorngren

When I'm searching specific data in datatables, it's not returning the json (invalid json)

You will need to look at your server script to determine why its not returning a result. With server side processing enabled the server script is responsible for handling the searching and sorting of the data and returning the proper JSON response.

Kevin

michaeljulyus

@kthorngren I know that, so I gave the server script code that I made because I think that code is correct. Is there something wrong with my server script?

kthorngren

Seems likes its just doing a select * which would return all records. If you want to use server side processing it is expected the server script return only the rows for the displayed page. So you need to take into account the parameters sent and build a query that returns one page of data. For example you may need to use things like SQL Limit and Offset. The server side processing protocol is documented here:
https://datatables.net/manual/server-side

The first question is do you need server side processing? If not you should remove that option and use client side processing. This FAQ will help you decide.

Kevin

michaeljulyus

But the attributes of the table are only two rows (id_kategori, nama_kategori), so I use select *. Yes, I need server side processing.

kthorngren

You are right, select * is fine to get the two columns.

Yes, I need server side processing.

It looks like your server script is only generating the query select * from t_kategori. Is that the case? If this is the only query then your script is returning all the rows. Does this take a long time?

For server side processing to work you need more than just select * from t_kategori. Your SQL query needs to order the data, limit the number of records returned and return the correct page.

If select * from t_kategori works for the initial query to return all the rows and you don't experience delay then maybe you don't need server side processing. I would consider removing serverSide: true to see how the table performs.

Kevin

michaeljulyus

Your SQL query needs to order the data, limit the number of records returned and return the correct page.

Yes, I know that. But I use the Ignited Datatables library to accommodate all of those works.

kthorngren

it's not returning the json (invalid json)

The Ignited Datatables library is a third party library. You will need to ask them for support as to why their library is not returning the JSON. Its the server side component that needs to be diagnosed.

Kevin

kleeh9091

This is the biggest crock of crap I've ever seen in a 3rd party lib, and I've seen a lot of crap. This is not even mentioned when I'm prompted to "create" a download lib that simply allows me to use the Buttons for export to Excel, PDF, etc... NOTHING IS mentioned that my dam query, which simply uses SQL to read from the server, is going to have the display of its data "throttled". NOTHING!! You guys have jacked up my production site data for 2 dam months with this BS. I SIMPLY WANT THE DAM EXPORT BUTTONS AND THE STYLING!!!! I do not need you to tell me how to filter or limit my dam data!!!!!!!!!!!!!
So I either get the dam Buttons, OR, I have to put up with this crap??? This is total BS!!!!!!!!!!!!!

colin

You guys have jacked up my production site data for 2 dam months with this BS.

Odd, seeing you only sent three messages in the past few hours.

As Kevin said in your other threads, there are docs and examples to explain these options.

If you want assistance, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Colin

allan

If you don't want any of the DataTables functionality, but do what the styling, then you could either:

1. Use a styling framework such as Bootstrap which will provide all the table styling you need
2. Or if you want the DataTables specific styling this is the source and you are welcome to use it since it is open source under the MIT license.

The export buttons will not work without DataTables though - they are not designed to (nor is it mentioned anywhere in the documentation that they would). They are specifically a plug-in for DataTables. If you don't want DataTables but do want PDF / Excel export, then our free software probably isn't what you need - unless you want to modify it to work without DataTables (which again its open source, you are welcome to do so). Instead, I'd suggest looking at pdfmake for PDF creation and JSSheet for Excel export.

Allan