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

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

Danny GunawanDanny Gunawan Posts: 5Questions: 2Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 GunawanDanny Gunawan Posts: 5Questions: 2Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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 GunawanDanny Gunawan Posts: 5Questions: 2Answers: 0

    Wow, it a great solution. It works now. :D

    Only a small correction is needed

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

    Thank you for your help, Allan.

This discussion has been closed.