Getting datatable to correctly show created values

ken.leech

I am using DataTables and the corresponding Editor to allow the user to create new entries within a MySQL database.

My editor definition is as follows:

var editor = new $.fn.dataTable.Editor( {
            ajax: "https://secure.procon3.co.nz/Webforms/shem/organisationLocality.php",
            table: "#tbTableMaintenance",
            idSrc: "id",
            fields: [
                {label: "ID:", data: "id", name: "id", type: "hidden"}, 
                {label: "Organisation:", data: "organisation_id", name: "organisation_id",
                    type: "select", options: maintainReferenceTables.getOrganisationList()
                },
                {label: "Locality:", data: "locality_id", name: "locality_id",
                    type: "select", options: maintainReferenceTables.getLocalityList()
                }
            ]

And my datatable is defined as follows:

var dataTable = $("#tbTableMaintenance").DataTable({
            destroy: true,
            stateSave: true,
            title: "Organisation Localities",
            ajax: {
                url: "https://secure.procon3.co.nz/Webforms/shem/getReferenceTable.php?table=organisationLocality",
                cache: false,
                dataType: "json",
                error: function(xhr,status,errtext) {
                    alert ("Error Routine Called "+status+errtext);
                    alert (JSON.stringify(xhr));
                }
            },
            columns: [
                {
                    title: "Select", 
                    data: "id",
                    orderable: false, 
                    render: function (value){
                        return "<img src='select.png' />";
                    }
                },
                {title: "Organisation", data: "organisation_name"},
                {title: "Locality", data: "locality_name"}
            ],
            dom: "Tfrtip",
            tableTools: {
                sRowSelect: "os",
                sRowSelector: 'td:first-child',
                aButtons: [
                    { sExtends: "editor_create", editor: editor },
                    { sExtends: "editor_remove", editor: editor }
                ]
            }
        });

The PHP code used to add a new entry to the table (organisationLocality.php) is as follows:

$data = $_POST["data"];

        if ($data["organisation_id"] == "") {
            array_push($fieldErrors, ["name"=>"organisation_id", "status"=>"This is a required field"]);
        }

        if ($data["locality_id"] == "") {
            array_push($fieldErrors, ["name"=>"locality_id", "status"=>"This is a required field"]);
        }

        if (count($fieldErrors) > 0) {
            echo json_encode(["fieldErrors"=>$fieldErrors]);
            exit;
        }

        // We need to return the name of the organisation and locality so it can be displayed
        
        $sql = "select name from organisation where id = " . $data["organisation_id"];
        $result = mysqli_query($conn, $sql);
        $organisation = $result->fetch_assoc();

        $sql = "select name from locality where id = " . $data["locality_id"];
        $result = mysqli_query($conn, $sql);
        $locality = $result->fetch_assoc();
        
        $sql = "insert into organisation_locality (organisation_id, locality_id) values (".
        $data["organisation_id"] . ", " .
        $data["locality_id"] . " " .
        ")";
        $result = mysqli_query($conn, $sql);
        if (!$result) {
            echo json_encode(["error"=>mysqli_error($conn)]);
            exit;
        }

        $data["organisation_name"] = $organisation["name"];
        $data["locality_name"] = $locality["name"];

        echo json_encode ($data);

When the user "creates" a new entry in the table, it correctly allows the user to select the two values required from a drop-down list, and adds the data successfully to the database. However it return the message "Requested unknown parameter name 'organisation_name' for row 6.

I can get rid of the message by adding "organisation_name" (and "locality_name) as hidden fields in the editor instance, but although the error message goes away, the fields within the datatable are not populated with the selected values. Any idea what I am doing wrong please?

ken.leech

BTW I have checked that the PHP routine really is returning valid values for "organisation_name" and "locality_name".