Cannot fix "Invalid JSON response"

saligiayys

**DataTables warning: table id=js-dataTable-timesheet - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1**:
Hi there, please save me~~~
I am tring to pass a data to PHP side by using ajax. The data is a date that I got when I click one date on a datetime picker. I'd like use the date to filter the datatable.
Unfortunately, no matter how I pass the date, including using $.ajax(), or js original ajax, I coulde not make it~
It keep showing invalid json, but I have used JSON Lint and it shows valid.
I really appreciate your help! Thank you!

//in js

$(document).ready(function () {
    editor = new $.fn.dataTable.Editor({
        ajax: "inc/backend/editor_controllers/timesheetEdit.php",
        table: "#js-dataTable-timesheet",
        fields: [{
            label: "Job Number:",
            name: "job_number"
        }, {
            label: "Address:",
            name: "address"
        }, {
            label: "Date:",
            name: "date",
        }, {
            label: "Start Time:",
            name: "start_time"
        }, {
            label: "End Time:",
            name: "end_time"
        }, {
            label: "Hours:",
            name: "hours"
        }
        ]
    });

    $('#js-dataTable-timesheet').DataTable({
        searching: false,
        // dom: "Bfrtip",
        dom: "Btp",
        ajax: {
            url: "inc/backend/editor_controllers/timesheetEdit.php",
            // dataType: 'json'
        },
        columns: [
            { data: "job_number" },
            { data: "address" },
            { data: "date" },
            { data: "start_time" },
            { data: "end_time" },
            { data: "hours" }
        ],
        select: true,
        select: 'single',
        buttons: [
            //disable the create button, use the one built myself
            // { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
        destroy: true,
    });
    
    //js.ajax
    var xml = new XMLHttpRequest();
    xml.onreadystatechange = function () {
        if (this.readyState == 4 && this.status == 200) {
            console.log(this.responseText)
        }
    }

    xml.open("POST", "inc/backend/editor_controllers/timesheetEdit", true);
    xml.setRequestHeader("Content-type","application/x-www-form-urlencoded");
    xml.send("date_picker=2022-11-07");   //even I don't use variable here it still does not work

}

//in php
Editor::inst($db, TBL_TIMESHEET)
    ->fields(
        Field::inst('job_number'),
        Field::inst('address'),
        Field::inst('date'),
        Field::inst('start_time'),
        Field::inst('end_time'),
        Field::inst('hours')
    )
    ->where('user_login', $_SESSION['user_login'], '=')
    ->where('date', $_POST['date_picker'], '=')
    ->debug(true)
    ->process($_POST)
    ->json();

//json response
{"data":[{"DT_RowId":"row_49","job_number":"15394","address":"134 Oceanview","date":"2022-11-07","start_time":"12:00:00","end_time":"13:00:00","hours":"1.00"},{"DT_RowId":"row_51","job_number":"16555","address":"26 Takapu Street Matua","date":"2022-11-07","start_time":"08:00:00","end_time":"17:00:00","hours":"8.50"}],"options":[],"files":[],"debug":[{"query":"SELECT  `id` as 'id', `job_number` as 'job_number', `address` as 'address', `date` as 'date', `start_time` as 'start_time', `end_time` as 'end_time', `hours` as 'hours' FROM  `timesheet` WHERE `user_login` = :where_0 AND  `date` = :where_1 ","bindings":[{"name":":where_0","value":"scheduleradmin","type":null},{"name":":where_1","value":"2022-11-07","type":null}]}]}

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Try:

        ajax: {
            url: "inc/backend/editor_controllers/timesheetEdit.php",
            data: function (d) {
                d.date_picker = '2022-11-07';
            }
        },

You don't need your own XMLHttpRequest - DataTables will make the Ajax request to get the data for you.

If that doesn't work, can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

saligiayys

It works! Thank you very very much Allan!! I need to read the document entirely again!

saligiayys

May I ask one more question please?
I can select date and the table can render data for that date now.
But each time I click a date, the width of rows changes wider and wider until maximum(like the picture below).

What I did is that wrapped the $('#js-dataTable-timesheet').DataTable({...}) again in a onchange event function when I select a date from the calendar I used. Was that right? Thank you so much!

    const flatVar = $("#calendar").flatpickr({
        defaultDate: "today",
        // When click a date on the calendar, render the data for that day
        onChange: function () {
            $('#js-dataTable-timesheet').DataTable({
                searching: false,
                // dom: "Bfrtip",
                dom: "Btp",
                ajax: {
                    url: "inc/backend/editor_controllers/timesheetEdit.php",
                    type: "POST",
                    dataType: 'json',
                    data: function (d) {
                        // d.default_date_picker = "2022-11-07"
                        d.date_picker = $('#calendar').val()
                    }
                },
                columns: [{
                    data: "job_number"
                },
                {
                    data: "address"
                },
                {
                    data: "date"
                },
                {
                    data: "start_time"
                },
                {
                    data: "end_time"
                },
                {
                    data: "hours"
                }
                ],
                select: true,
                select: 'single',
                buttons: [
                    //disable the create button, use the one built myself
                    // { extend: "create", editor: editor },
                    {
                        extend: "edit",
                        editor: editor
                    },
                    {
                        extend: "remove",
                        editor: editor
                    }
                ],
                // width: 100% !important,
                destroy: true,
            });
            // $.fn.dataTable.tables({visible:true, api: true}).columns.adjust();
        }
    });

kthorngren

Uness you are changing the Datatables configuration you don't need to reinitialize it with the destroy option. Just initialize it once then use ajax.reload() in the event handler to fetch the new set of data.

Kevin

saligiayys

Thank you very much kthorngren