What response does the editor expect when an update is submitted

robobrief

I am using a custom ajax update routine. I am not using the built-in database updating routine, I am simply using the table and form to display and capture the relevant detail. When I submit the update via the table editor I can see through XHR that it successfully posts the data to the correct ajax url and that that page successfully processes what I need it to process. What response does the routine have to send back to the editors to let it know that the commands have been successful? At the moment I am getting a status code 200 OK but the form doesn't close and reports an error so I am assuming it is looking for a specific response?

robobrief

I have answered my own question (I think). It is expecting a json which I have now given it. The only problem now is that after submission the row immediately disappears from the table. If I refresh the page I can see that ajax call has done its job and the database has updated. Can anyone help.

This is the js:

       var editor; 
    $(document).ready(function() 
    {       
        "use strict";       
        editor = new $.fn.dataTable.Editor
        (
            {    
                table: "#example",
                fields: 
                [ 
                    {
                        label: "ID:",
                        name: "ul.ID"
                    },
                    {
                        label: "First Name:",
                        name: "m_first_name.meta_value"
                    }, 
                    {
                        label: "Last Name:",
                        name: "m_last_name.meta_value"
                    }, 
                    {
                        label: "Auto EOT:",
                        name: "m_auto_eot_time.meta_value"
                    },
                    {
                        label: "Nicename:",
                        name: "ul.user_nicename"
                    }   
                ],
                ajax: 
                { 
                    edit: "edit-lastname.php?id=_id_"
                }
            }
        );
        $('#example').DataTable
        (
            {
                dom: "Bfrtip",
                ajax: "users.php",
                type: 'POST',
                columns: 
                [
                    { data: "ul.ID" },
                    { data: "m_first_name.meta_value" },
                    { data: "m_last_name.meta_value" },
                    { data: "m_auto_eot_time.meta_value" , editField: "m_eot_time.meta_value"},         
                    { data: "ul.user_nicename"} 
                ],
                select: true,
                buttons: 
                [
                    { extend: "edit",   editor: editor }
                ]
            }
        );
        editor.on ( 'submitSuccess', function () 
        { 
            //THIS IS JUST HERE AS A MESSAGE TO MYSELF FOR DEBUGGING
                    alert( JSON.stringify(editor.get()));       
        });     
    });

This is the code in edit-lastname.php (the script that does the updating of the DB):

    $user_id = substr($_GET['id'],4);
        //CODE IS HERE WHICH CORRECTLY UPDATES THE DATABASE
    header('Content-Type: application/json');
    echo json_encode($_POST);   

And in case it's important here the code for users.php

include( dirname(__FILE__). "/php/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
Editor::inst( $db, 'users as ul' )
    ->field( 
        Field::inst( 'ul.ID' ),
        Field::inst( 'ul.user_login' ),
        Field::inst( 'ul.user_pass' ),
        Field::inst( 'ul.user_nicename' ),
        Field::inst( 'ul.user_email' ),
        Field::inst( 'm_first_name.meta_key' ),
        Field::inst( 'm_first_name.meta_value' ),   
        Field::inst( 'm_last_name.meta_key' ),
        Field::inst( 'm_last_name.meta_value' ),    
        Field::inst( 'm_custom_fields.meta_key' ),
        Field::inst( 'm_custom_fields.meta_value' ),
        Field::inst( 'm_auto_eot_time.meta_key' ),
        Field::inst( 'm_auto_eot_time.meta_value' ) 
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )     
            ->getFormatter( 'Format::date_format_to_sql', Format::DATE_EPOCH )
            ->setFormatter( 'Format::date_sql_to_format', Format::DATE_EPOCH )          
    )
    ->leftJoin( 'usermeta as m_first_name', 'm_first_name.user_id', '=', 'ul.ID AND m_first_name.meta_key = \'first_name\''  )
    ->leftJoin( 'usermeta as m_last_name', 'm_last_name.user_id', '=', 'ul.ID AND m_last_name.meta_key = \'last_name\''  )
    ->leftJoin( 'usermeta as m_custom_fields', 'm_custom_fields.user_id', '=', 'ul.ID AND m_custom_fields.meta_key = \'custom_fields''  )
    ->leftJoin( 'usermeta as m_auto_eot_time', 'm_auto_eot_time.user_id', '=', 'ul.ID AND m_auto_eot_time.meta_key = \'auto_eot_time\''  )
    ->where ('m_last_name.meta_value','SMITH', 'LIKE')
    ->process($_POST)
    ->json();

allan

The only problem now is that after submission the row immediately disappears from the table.

This would happen if the server doesn't contain the row information for the edit that was edited. That can happen if your where condition doesn't match the row's new data. Might that be the case here?

Allan

robobrief

Allan

Thanks very much for taking the time to respond. Yes, that is exactly the problem. I have now made edit-lastname.php return a json record in EXACTLY the same format as that used to build the table and it works perfectly! It just took me a little time to figure out what the required format was.

For anyone who didn't guess, I am working with the wordpress user and user_meta tables so unfortunately I cannot take advantage of much of the built-in database editing functionality. This is not only because of the structure, but also because I need to use a lot of the wordpress actions to make sure nothing goes awry. That said, the admin page I have setup using editor datables is still way better than using the wordpress users admin screen!

Thank you once again.

In case it's of any assistance to anyone else who visits the forum with a wordpress query, here is my code for edit-lastname.php

    //GET ACCESS TO THE WORDPRESS PLATFORM
        $path = $_SERVER['DOCUMENT_ROOT'];   
    require_once $path . '/wp-config.php';
    require_once $path . '/wp-load.php';
    require_once $path . '/wp-includes/wp-db.php';
    require_once $path . '/wp-includes/pluggable.php'; 
    global $wpdb; 
        //GET THE CONTENTS OF THE FORM
    $user_id = substr($_GET['id'],4);
    $user_last_name = $_POST['data']['row_' . $user_id]['m_last_name']['meta_value'];
        //UPDATE WORDPRESS WITH THE CONTENTS
    update_user_meta ($user_id, 'first_name', $user_first_name);    
    update_user_meta ($user_id, 'last_name', $user_last_name);
       //NOW GET THE USER RECORD FROM WORDPRESS TO RETURN BACK TO EDITOR
    $user_first_name = get_user_meta($user_id, "first_name", true);
    $user_last_name = get_user_meta($user_id, "last_name", true);   
    $user_custom_fields = get_user_meta($user_id, "custom_fields", true); 
    $user_auto_eot_time = get_user_meta($user_id, "auto_eot_time", true);
    //BUILD THE JSON    SO THAT EDITOR UNDERSTANDS IT
    $str_json = "";
    $str_json .= 
    '{"data":
        [
            {
                "DT_RowId":"row_' . $user_id . '",
                "ul":
                {
                                "ID":"' . $user_id . '"
                },
                "m_first_name":
                {
                    "meta_key":"first_name",
                    "meta_value":"' . $user_first_name . '"
                },
                "m_last_name":
                {
                    "meta_key":"last_name",
                    "meta_value":"' . $user_last_name . '"
                },
                "m_s2member_custom_fields":
                {
                    "meta_key":"custom_fields",
                    "meta_value":"' . $user_custom_fields . '"
                },
                "m_s2member_auto_eot_time":
                {
                    "meta_key":"auto_eot_time",
                    "meta_value":"' . $user_auto_eot_time . '"
                }
            }
        ]
    }'; 
    header('Content-Type: application/json');   
    echo $str_json; 

allan

Really interesting - thanks for sharing that with us!

Allan

robobrief

I had fixed this but having added some functionality I now have a different problem. The form submits the changes and the ajax does it's job successfully, updated the database and returning a json of the newly updated row. The new values are written straight back into the table and all looks good. But if I open up and editor form for the same row again, some of the fields are now blank - they exist in the table but are not "pulled through" on to the form. Is there anything obvious I should look at?

allan

Is the JSON response to the edit identical in structure to the data that is used to load the DataTable in the first place? Can you show me a sample of the JSON that is used to load the table and also the edit JSON response?

Allan

robobrief

Sorry about the delay. I am distracted with the kids and half-term holidays. I have solved the problem. The original data used to draw the table included a serialised field from Wordpress. I used data: function() to extract the value required. The data that was returned following a successful edit had already extracted the data and although the function catered for this it didn't work. I returned the data in a serialised format and it worked fine. I was just struggling with php which kept passing it as an array - I ended up using stringify and it worked perfectly. If anyone wants me to post the final code then please just reply.