Using Editor on a WordPress table in admin

socialink

Hi folks

Looking for a way to have a client directly edit a particular table in the WP admin section, and thinking of licensing Editor for this purpose.

Doing our discovery and following this example I see you recommend a whole other database, but our use case here is to have a user edit a custom table directly (eg wp_custom_table) in the main WP install.

Is that a bad idea for an authenticated backend user? Do you have any tips or updated (that blog post is from 2019) recommendations around this kind of integration? Thank you.

allan

Yes, you can use Editor to directly update a WP defined database table. I don't see a problem with that, as long as you understand the structure of the table and any post write hooks that WP might use. If it is a custom table you have defined, I'd just treat it like any other table, and go ahead and write to it.

I should qualify this by saying obviously I don't know what your table is, what dependencies it has, and what it does, but on an initial pass, it sounds okay.

Allan

socialink

Thanks Allan.

I got it to work almost perfectly. The issue here is that putting the php code in its own file (eg /mysite-ajax.php) potentially opens a security hole for allowing someone not logged in to modify db.

In WP, you can call AJAX functions with a url and then an action, and preface it with wp_ajax to make sure the WP system checks if the user is logged in (https://developer.wordpress.org/reference/hooks/wp_ajax_action/).

In real terms this means when you make ajax calls in a WP plugin/theme, your URL is always /wp-admin/admin-ajax.php and you supply an action, and add a 'hook' function to that action. Then in that 'hook' function I can do all kinds of other stuff like log who made the change, etc etc.

Is there a way to get the have the JS go to a URL and 'action' and I call all the editor code within there? EG

var editor = new $.fn.dataTable.Editor( {
    ajax: wp.ajax_url,
    data: {
        'action': 'ajax_my_action',
        session_token:   token,
        other_data: whatever
    },
    table: "#my_table_edit",
    fields: [ {
            label: "ID",
            name: "id"
        }, {
            label: "Created",
            name: "created"
        }
        // etc
} );

const edit_user_table = $('#my_table_edit').DataTable( {
    dom: "Bfrtip",
    ajax: wp.ajax_url,
    data: {
        'action': 'ajax_my_action',
        session_token:   token,
        other_data: whatever
    },
    columns: [
        { data: "id" },
        { data: "created" },
        // etc
    ],
    select: true
} );

Thank you.

socialink

BTW, it seems like whereas 'regular' DataTables put buttons and the search inside a well-laid out dt-layout-row, the Editor one does not. Not sure if its a bug or I'm doing it wrong but I'm more or less following the default examples for now to learn the software.

"regular" DT:

editor DT:

kthorngren

Is there a way to get the have the JS go to a URL and 'action' and I call all the editor code within there?

Looks like you need to put the data object inside the Datatables ajax and the Editor `e-option ajax options. See the docs for both. For example:

var editor = new $.fn.dataTable.Editor( {
  ajax: {
    url: wp.ajax_url,
    data: {
      'action': 'ajax_my_action',
      session_token:   token,
      other_data: whatever
    }
  },
....

It seems like whereas 'regular' DataTables put buttons and the search inside a well-laid out dt-layout-row, the Editor one does not.

Datatables controls the layout of the buttons, etc using either the layout or dom option. Editor is an extension to allow for editing of the Datatables data. Editor does not control the buttons, search element, etc.

Looks like the "regular" DT screenshot is using the layout and other is using the Datatables dom option. You can use either but layout was introduced in Datatables 2 and is the preferred option going forward.

Kevin

socialink

Thanks for the help on the layout and the pointers on AJAX. Unfortunately passing something like this:

ajax: {
    url: my_ajax.ajaxurl,
    type: 'GET',
    dataType: 'json',
    data: {
        'action': 'ajax__edit_tables',
        //  how do I attach the actual DT data
    }
},

is a bit funky. First it only works to get it into the WP "system" and only loads the table with GET (not POST) strangely, and does not save CRUD actions. With POST it neither loads nor save CRUD actions.

Changing ->process( $_POST ) to $_GET seems to not work, but process($_POST) does work in the initial load even if CRUD updates are sent via $_GET! Haha.

I have no idea where exactly things are going awry. Is it the 'action' variable as outlined here that we're overwriting?

allan

If I recall correctly, WP uses the action parameter itself. You need to tell Editor to use a different name, which you can do using actionName. Then in the PHP Editor instance there is a matching actionName() method which you use to tell it what HTTP parameter to look for.

Allan

socialink

Huh, it seems like the 'action' isn't getting picked up even though I did the actionName; its just sending a Select on inline editor and not the actual update code?

Again it is reading perfectly at page load, but not doing CRUD.

here is the request:

XHRPOST
https://mysite.org/wp-admin/admin-ajax.php

data[row_8][email]  "mytesting@mytesting.org"
DTeditor    "edit"
action  "ajax_edit_tables"

here is the response

XHRPOST
https://mysite.org/wp-admin/admin-ajax.php
[HTTP/2 200  367ms]

data    [ {…}, {…}, {…} ]
options []
files   []
debug   [ "Editor PHP libraries - version 2.3.2", {…} ]
0   "Editor PHP libraries - version 2.3.2"
1   Object { query: "SELECT `id` as 'id', `created` as 'created', `email` as 'email', `employee_id` as 'employee_id', `employer` as 'employer' FROM `wp_userbase` ", bindings: [] }
query   "SELECT `id` as 'id', `created` as 'created', `email` as 'email', `employee_id` as 'employee_id', `employer` as 'employer' FROM `wp_userbase` "
bindings    []

Code here (based on example):

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'wp_userbase' )
    ->actionName('DTeditor')
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'created' ),
        Field::inst( 'email' ),
        Field::inst( 'employee_id' ),
        Field::inst( 'employer' ),
    )
    ->debug(true)
    ->process( $_POST )
    ->json();

JS

const edit_user_table = $('#table_edit_users').DataTable( {
    ajax: {
        url: my_ajax.ajaxurl,
        type: 'POST',
        // dataType: 'json',
        data: {
            'action': 'ajax__edit_tables',
        }
    },
    actionName: 'DTeditor',

allan

Your Javascript shows the DataTables initialisation. You need to set actionName on the Editor Javascript instance. You'll need ajax.data on it as well.

Allan

socialink

Hi all,

I solved it - I know I've been taking a lot of your forum time so appreciate it.

It did seem like the PHP library was not reading an updated action name set by ->ActionName based on my dissection of Editor.php; it was always trying to look for the parameter named "action" in the function action() on line 97:

public static function action($http, $name = 'action')
    {
        if (!isset($http[$name])) {
            return self::ACTION_READ;
        }

        switch ($http[$name]) {
            case 'create':
                return self::ACTION_CREATE;
            case 'edit':
                return self::ACTION_EDIT;
            case 'remove':
                return self::ACTION_DELETE;
            case 'search':
                return self::ACTION_SEARCH;
            case 'upload':
                return self::ACTION_UPLOAD;
            default:
                throw new \Exception('Unknown Editor action: ' . $http['action']);
        }
    }

So on line 1000 of Editor.php, I changed

$action = Editor::action($data)

to

$action = Editor::action($data,$this->_actionName);

and it works great. If i'm doing it wrong I'd love to know but I couldn't find anywhere in Editor.php that actually used the updated actionName instead of the hardcoded 'action' (which interferes with WP).

With that I can say that I've managed to totally integrate this into the WP backend securely.

allan

Gah! Thank you for letting me know about that - I am sorry for that error. I've committed a fix which will be in the next release.

Allan