"Requested unknown parameter" error for different Editor instances

pansengtat

I have been using Datables/Editor for some time, and while I usually could figure out problems like "Requested unknown parameter" (see http://www.datatables.net/manual/tech-notes/4) for some cases, this is one that have me stumbed not because I don't know the cause, but rather the best next course-of-action.

The page that I intend to load needs to load the ID of a MySQL table and then hide it.
In the beginning, the JS page is designed in a way such that there is no need to load the ID, even though the way the editor_create and editor_edit instances are two separate Editor instances in the JS file.

Editor instance:

editor = new $.fn.dataTable.Editor({
            ajax: "myEditor.php",
            table: "#myTable",
            fields: [
                {
                    label:      "Project Name: ",
                    name:       "myTable.ProjectNo",
                    type:       "select"
                }, {
                    // display all additional data fields here
                }, {
                    label:      "ID (Hidden): ",
                    name:       "myTable.ID",
                    type:       "hidden"
                }
            ],
            i18n: {
                edit: {
                    title:  "Update"
                }
            }
        });

Adder instance:

adder = new $.fn.dataTable.Editor({
            ajax: "myEditor_Add.php",
            table: "#myTable",
            fields: [
                {
                    label:      "Project Name: ",
                    name:       "myTable.ProjectNo",
                    type:       "select"
                    def:        "1",
                    fieldInfo:  "Ensure the Project is made before choosing here."
                }, {
                    // Load all other data fields here
                }
            ],
            i18n: {
                create: {
                    title:      "New entry"
                }
            }
        });

The problem now is that I have created a button that requires to fetch the ID of a selected row and pass to a backend function/file.
Thus, whenever the editor_create instance is used, it could add the entry into the database, but it would keep prompting the "Requested unknown parameter at 'myTable.ID' at row {integer}" error message, because the ID data property would not have been existent in order to be displayed/re-drawn onto the DataTable (the ID could still be incremented correctly inside the database).

Some PHP code are here so that anyone else can probe into it, feel free to comment:

// Editor
$data = Editor::inst($db, 'myTable', 'ID')
        ->where('myTable.ID', $CONST_RESERVED_ID, "!=")
        ->field(
            Field::inst('myTable.ID')->set(false),
    Field::inst('myTable.ProjectID')->validator('Validate::notEmpty'),  
    // insert others here
        )
        ->leftJoin('myProjectTable', 'myTable.ProjectID', '=', 'myProjectTable.ID')
        // other left joins here
        ->process($_POST)
        ->data();

// Adder
$data = Editor::inst($db, 'myTable', 'ID')
        ->field(
            Field::inst('myTable.ProjectID')->validator('Validate::notEmpty'),  
            // insert others here
        )
        ->leftJoin('myProjectTable', 'myTable.ProjectID', '=', 'myProjectTable.ID')
        // other left joins here
        ->process($_POST)
        ->data();

I would like to know, if there are workarounds on this issue of needing to load and hide the ID but somehow get the ID into the Ajax when editor_create finished adding to the DB and returns the editor_edit data.

allan

Hi,

I'm not entirely clear why you need myTable.ID as a hidden field? It will be submitted automatically as id and read form the JSON as DT_RowId since it is the primary key for your table.

Could you clarify why you need it as a separate parameter?

Allan

pansengtat

The reason why I need the ID as a hidden field is because I intend to use that value and pass it through a server-side script via Ajax/POST, which I intend to update other tables in the database with this ID. Choosing data from other hidden columns would not be possible as the other hidden columns don't contain a unique value. If there is another way of getting the ID of the selected row and then pass that ID through Ajax to that server-side script that I mentioned, then that would have solved the pop-up error I just encountered.

NOTE: the ID mentioned here refers to the unique identifier of the table of the database, not to be confused with the row ID of the displayed table

allan

NOTE: the ID mentioned here refers to the unique identifier of the table of the database, not to be confused with the row ID of the displayed table

Possibly that is where I am confused then. You have:

 Editor::inst($db, 'myTable', 'ID')

And:

 Field::inst('myTable.ID')

In one of the editors above. Those are the same thing. DT_RowId in the JSON is that id, and id in the submitted data is also that value.

Allan

pansengtat

I don't know if this is considered hacker-ish, but I found a workaround. Albeit very risky, but...

I added a hidden field for myTable.ID in the editor_create instance, and then used a Field::inst('myTable.ID') in the PHP file for the adder, as follows. Forgot to mention that the ID in myTable is auto-incremented and unique.

// Javascript segment for adder
adder = new $.fn.dataTable.Editor({
            ajax: "myEditor_Add.php",
            table: "#myWebTable",
            fields: [
        {
            // insert data fields here
        }, {
            label:      "Table ID to be inserted by auto-increment column (Hidden): ",
            name:       "myTable.ID",
            type:       "hidden"
        }
    ],
    i18n: {
        create: {
            title:      "Updated Add Button"
        }
    }
});

// PHP file for adder
$data = Editor::inst($db, 'myTable', 'ID')
        ->field(
            Field::inst('myTable.ID'),
            Field::inst('myTable.SomeOtherID')->validator('Validate::notEmpty'),    
            // more stuff added
        )
        ->leftJoin('myOtherTable', 'myTable.SomeOtherID', '=', 'myOtherTable.ID')
        // more left joins
        ->process($_POST)
        ->data();

It somehow doesn't produce any error when added, and that is only one user using the system (i.e. me... not sure if a race condition can happen when there are two or more). When counter-checked against the DB, the ID is incremented correctly and the data is reflected into the respective columns correctly as well. But... I have a gut feeling that the way I did it here is bound to cause possible future issues... I don't know why, but while I did solve this issue, I believed there might be a less hack-ish way of going about it.

EDIT: Turns out that this method only works on a localhost. But if this is to be done via a web server (live), it will return this:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'ID' at row 1