Cannot edit row when the primary key is a string contains space

Danny Gunawan

Hi,
I have a table that contains 2 columns:

+-------------+-----------+
| category    |  level   |
+-------------+-----------+
| Cat         |     1     |
| Cat A       |     2     |
| Cat B       |     1     |
| ....        | ...       |
+-------------+-----------+

on Client Side I create a simple editor:

editor = new $.fn.dataTable.Editor({
        ajax: url + "json",
        table: "#detail",
        fields: [
            {
                label: "Category",
                name: "category",
                type: "display"
            },
            {
                label: "level",
                name: "level"
            },
        ]
    });

on Server Side (PHP):

$DB  = $this->editorlib->getDatabase();
$ed = new Editor($DB, "category_table","category");
$ed->debug(true);
$ed->fields(
    Field::inst('category')->set(false),
    Field::inst('level')
);
$ed->process($_POST);
$ed->json();

When I tried to update Cat A level value to another value (3), the row is not updated but a new row with category value '0', and level value 3 inserted instead.

+-------------+-----------+
| category    |  level    |
+-------------+-----------+
| Cat         |     1     |
| Cat A       |     2     |
| Cat B       |     1     |
| 0           |     3     |
+-------------+-----------+

Debug log:

{
   "data":[
      {
         "DT_RowId":"row_0",
         "category":"0",
         "level":3
      }
   ],
   "debug":[
      {
         "query":"SELECT  category as \"category\" FROM  category_table WHERE category = :where_0 ",
         "bindings":[
            {
               "name":":where_0",
               "value":"0",
               "type":null
            }
         ]
      },
      {
         "query":"UPDATE  category_table SET  level = :level WHERE category = :where_0 ",
         "bindings":[
            {
               "name":":level",
               "value":"3",
               "type":null
            },
            {
               "name":":where_0",
               "value":"0",
               "type":null
            }
         ]
      },
      {
         "query":"SELECT  category as \"category\", level as \"level\" FROM  category_table WHERE category = :where_0 ",
         "bindings":[
            {
               "name":":where_0",
               "value":"0",
               "type":null
            }
         ]
      }
   ]
}

From the log above we can see that the binding for ':where_0' is incorrect. It should be "Cat A".

However if I edit the 'Cat' Category, it works fine.

If it's possible, I would like to avoid changing the database structure and add a new primary key.

Thank you.
Danny.

allan

Interesting one. The problem with a space is that DataTables uses the primary key column as a DOM id. But as a straight string, that makes it look like a sub-element CSS selector.

We had a similar issue a while back with colons in an ID and what I came up with at the time ended up being this function in Editor:

var __dtjqId = function ( id ) {
    return typeof id === 'string' ?
        '#'+id.replace( /(:|\.|\[|\]|,)/g, '\\$1' ) :
        '#'+id;
};

So basically its trying to escape characters to be usable as a selector.

I've just done a little experimenting, and I think if you make that replace line:

        '#'+id.replace( /(:|\.|\[|\]|,| )/g, '\\$1' ) :

then it should (fingers crossed) work! Could you try that little tweak in your setup? (all of the pkey's I have in my demo db are integers).

Allan

Danny Gunawan

Thank you for your reply,
I tried to replace it, but still did not work. I put a breakpoint to that line, but i was not executed.

I think the problem is the PHP's $_POST variable.
Look at this form data that was sent by Editor Client to the server:

data[row_Cat A][category]: Cat A
data[row_Cat A][level]: 3
action: edit

Result of print_r($_POST):

Array
(
    [data] => Array
        (
            [0] => Array
                (
                    [category] => Cat A
                    [level] => 3
                )

        )

    [action] => edit
)

It looks like that PHP is unable to parse the array's key correctly when the key contains spaces.

allan

Really interesting. What you might need to do is submit the data to the server as JSON rather than HTTP parameters. The last example on the ajax.data page shows how that can done.

Then on the server-side, instead of using $_POST you can then do:

$json = file_get_contents('php://input');
$data = json_decode($json);

And pass $data into the ->process() method.

Allan

Danny Gunawan

Wow, it a great solution. It works now.

Only a small correction is needed

$data = json_decode($json, true); // <-- decode as a associative array

Thank you for your help, Allan.