Inserting new row on edit

globalplane

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I'm working on setting up Editor/PHP Library. I'd like to have a history of every change made, thus I have a second SQL table which lists every edit, with timestamp and username. Currently, the PHP library pulls from a MySQL VIEW which returns the newest item from the history table along with the data from the row it is associated with.

So to "edit" the table, I actually want it to just insert a new row into the history table with the relevant information. Could you tell me how this could be done with the library?

Small, related questions:
* Where is the documentation on how the PHP library handles updating the database when receiving an edit from the client?
* Is there any built in history functionality?

allan

Hi,

Have you read over this part of the Editor PHP documentation. It is doing more or less exactly what you want.

Allan

globalplane

Oh perfect! I hadn't looked through the manual well enough.

globalplane

Also, what would be the best place to add a check that the user is logged in before performing an INSERT? If I have a variable set to true if logged in, then something like:

if($_SESSION['loggedin']==true) {

$db->insert( 'staff-log', array(
        'user'   => $_SESSION['username'],
        'action' => $action,
        'values' => json_encode( $values ),
        'row'    => $id,
        'when'   => date('c')
    ) );

}

I'd prefer something built-in which gives feedback to the client-side, but I couldn't really understand the example in the Security section:

Editor::inst( $db, 'staff' )
    ->fields(
        Field::inst( 'name' )
            ->set( $_SESSION['access']['editing'] )
        Field::inst( 'location' )
            ->set( $_SESSION['access']['editing'] )
        Field::inst( 'salary' )
            ->get( $_SESSION['access']['admin'] )
            ->set( $_SESSION['access']['admin'] )
    );

globalplane

I'll create a new thread for this.

globalplane

I'm running into a good amount of trouble trying to get this to work. I have this code in ->on() just as a test:

$editor = Editor::inst( $db, 'alldata', 'main_index' )
    ->fields(
        Field::inst( 'main_index' ),
        Field::inst( 'id', 'vid')
            ->getFormatter( function ( $val, $data ) {
                return '<iframe height="200" src="https://www.youtube-nocookie.com/embed/' . $val . '" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>';
            } ),
        Field::inst( 'channel', 'channelTitle' )
            ->searchPaneOptions( SearchPaneOptions::inst() ),
        Field::inst( 'language_name', 'language' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    null => "* Not yet set",
                    default => $val
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst() 
                ->render( function ( $str ) {
                    return (match ($str) {
                        null => "* Not yet set",
                        default => $str
                    });
                })
            ),
        Field::inst( 'title', 'title' ),
        Field::inst( 'difficulty', 'difficulty' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    null => "* Not yet set",
                    default => $val
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst() 
                ->render( function ( $str ) {
                    return (match ($str) {
                        null => "* Not yet set",
                        default => $str
                    });
                })
            ),
        Field::inst( 'enabled', 'enabled' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    '0' => "Disabled",
                    '1' => "Enabled",
                    default => $str
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->render( function ( $str ) {
                    return (match ($str) {
                        '0' => "Disabled",
                        '1' => "Enabled", 
                        default => $str
                    });
                })
            )
            
    )
    ->on( 'preEdit', function ( $editor, $id, &$values, &$row ) {
        echo("test");
        exit();
    } )
    
    
    ->write( false )
    ->debug(true, './debug.log')
    ->process( $_GET )
    ->json();

and the code in the on() never gets run. I tried preEdit, since it seems postEdit is supposed to be after a successful edit of the database, which I won't have, since the only edit will be the logging. I looked at the debug log, and there's no mention of INSERT or UPDATE. And I think it said preEdit comes before validation functions, so I'd prefer to somehow use those functions.

Couple other issues related to this:
* I need to insert into a different logging table depending on which of the three fields is edited. I tried putting ->on() after a Field instead of the Editor, but it gave an error.
* I only want to insert a log row for the field that was edited, but looking at the request sent from the client, it sends the other field values as well when sending the JSON with action: edit. Is that changeable?

I tried using submit: 'changed' in:

$('#library-table').on( 'click', '.editable', function (e) {
                    editor.inline( this, 
                        {
                            submit: 'changed'
                        }
                    );
                } );

but it still sends multiple fields in the request:

data[row_37987][difficulty]=4&data[row_37987][language]=0&action=edit

globalplane

Looks like the response from the server when trying to edit is basically empty, by the way. Just has the name and version number of the PHP library.

allan

I'll create a new thread for this.

I've added a reply to this one in your other thread.

->process( $_GET )

Have you configured Editor to send the data as a GET request? It will do a POST by default.

If you could give me a link to the page you are working on I can inspect it directly to see what is going on.

Looks like the response from the server when trying to edit is basically empty, by the way.

With the preEdit listener you have there I would expect it to just have test in the response body, and then not enact any of the changes to the data. That response, not being valid JSON, should cause Editor on the client-side to show system error.

Allan

globalplane

Have you configured Editor to send the data as a GET request?

I did have both server and client as GET, now they're both POST, still not working.

Here's a link:

https://comprehensibleinputwiki.com/ciwlibrary/vidadmin.php

test doesn't show up, and there's no error.

And here's the full server-side code:

<?php
    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);

    header('Content-Type: application/json; charset=utf-8');

    

include( "/var/www/comprehensibleinputwiki.com/html/editor-php/lib/DataTables.php" );


 
use 
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchPaneOptions;

function logChange ( $db, $action, $id, &$values, $table ) {
    echo($values);
    /*
    $db->insert( $table, array(
        'row_index'   => ,
        'action' => $action,
        'values' => json_encode( $values ),
        'row'    => $id,
        'when'   => date('c')
    ) );
    */
}

$editor = Editor::inst( $db, 'alldata', 'main_index' )
    ->fields(
        Field::inst( 'main_index' ),
        Field::inst( 'id', 'vid')
            ->getFormatter( function ( $val, $data ) {
                return '<iframe height="200" src="https://www.youtube-nocookie.com/embed/' . $val . '" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>';
            } ),
        Field::inst( 'channel', 'channelTitle' )
            ->searchPaneOptions( SearchPaneOptions::inst() ),
        Field::inst( 'language_name', 'language' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    null => "* Not yet set",
                    default => $val
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst() 
                ->render( function ( $str ) {
                    return (match ($str) {
                        null => "* Not yet set",
                        default => $str
                    });
                })
            ),
        Field::inst( 'title', 'title' ),
        Field::inst( 'difficulty', 'difficulty' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    null => "* Not yet set",
                    default => $val
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst() 
                ->render( function ( $str ) {
                    return (match ($str) {
                        null => "* Not yet set",
                        default => $str
                    });
                })
            ),
        Field::inst( 'enabled', 'enabled' )
            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    '0' => "Disabled",
                    '1' => "Enabled",
                    default => $str
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->render( function ( $str ) {
                    return (match ($str) {
                        '0' => "Disabled",
                        '1' => "Enabled", 
                        default => $str
                    });
                })
            )
            
    )
    ->on( 'preEdit', function ( $editor, $id, &$values, &$row ) {
        echo("test");
        exit();
        logChange( $editor->db(), 'create', $id, $values, 'mc_revs_enabled' );
    } )
    
    
    ->write( false )
    ->debug(true, './debug.log')
    ->process( $_POST )
    ->json();

allan

Oh - you have:

->write( false )

That disables everything apart from the reading of the data. That would do it . With that option preEdit will never fire, since it never gets that far!

Allan

globalplane

Oh wow, I'd forgotten about that line from when I copied the first example. I'm able to now fire the preEdit block.

Now that that works, do you mind helping with the other questions:

I tried preEdit, since it seems postEdit is supposed to be after a successful edit of the database, which I won't have, since the only edit will be the logging. ... And I think it said preEdit comes before validation functions, so I'd prefer to somehow use those functions.

Couple other issues related to this:
* I need to insert into a different logging table depending on which of the three fields is edited. I tried putting ->on() after a Field instead of the Editor, but it seems that function doesn't exist.
* I only want to insert a log row for the field that was edited, but looking at the request sent from the client, it sends the other field values as well when sending the JSON with action: edit. Is that changeable?

allan

And I think it said preEdit comes before validation functions, so I'd prefer to somehow use those functions.

Yes it does. The reason is to allow preEdit to add extra validation functions if needed (or preCreate). If you want an event after the data has been validated use the validatedEdit event mention in the events docs.

To confirm my understanding of the issue - you don't want to actually update the main table - you just want to insert that an insert / update / delete was requested into another table, and then presumably you have some other process that will approve those changes and update the main table?

That isn't really a situation that Editor was designed for. The validatedEdit event does not provide a way to cancel the action like preEdit does.

I need to insert into a different logging table depending on which of the three fields is edited. I tried putting ->on() after a Field instead of the Editor, but it seems that function doesn't exist.

Correct - on() is a method of the Editor class, not Field. However, based on the second question (see below) you can check which field was submitted.

I only want to insert a log row for the field that was edited, but looking at the request sent from the client, it sends the other field values as well when sending the JSON with action: edit. Is that changeable?

Yes - use the submit option of the form-options object (formOptions.main). Set it to changed to submit only the changed value.

The open question is how to stop Editor doing its insert / update on the main table, but after the validation... I'll likely need to make validatedEdit cancellable for that.

Allan

globalplane

To confirm my understanding of the issue - you don't want to actually update the main table - you just want to insert that an insert / update / delete was requested into another table, and then presumably you have some other process that will approve those changes and update the main table?

Sort of. When the user performs an "edit", it actually just adds a row to the second table with the new value. When the table is retrieved, it leftjoins the newest corresponding value from the second table. The main table never has to be changed.

The open question is how to stop Editor doing its insert / update on the main table, but after the validation... I'll likely need to make validatedEdit cancellable for that.

Actually, now I realize I can just update the main table (if I switch to using an actual table instead of a VIEW like I'm currently using), and ignore that column of the table later.

Yes - use the submit option of the form-options object (formOptions.main). Set it to changed to submit only the changed value.

Actually, I tried that, as per a previous comment, with no change:

I tried using submit: 'changed' in:

$('#library-table').on( 'click', '.editable', function (e) {
                    editor.inline( this,
                        {
                            submit: 'changed'
                        }
                    );
                } );

but it still sends multiple fields in the request:

data[row_37987][difficulty]=4&data[row_37987][language]=0&action=edit

I have three editable fields. Two selects and one checkbox. If I edit one of the selects, it sends data for both selects. If I edit the checkbox, it sends data for all three fields.

Possibly related to this thread.

globalplane

Well, I actually didn't have it exactly as you suggested. submit: changed was in inline(), but now I've added it where you suggested, but still same behavior.

allan

It will submit a value if it thinks the data has changed, which it must do for both difficulty and language there. I'm not such which of the two you have edited though - could you clarify that for me please? Could you also show me the data being loaded into the DataTable, plus the client-side configuration for both DataTables and Editor? Or if you can give me a link to the page, I'll be able to find that information and debug it.

Allan

globalplane

I'm not such which of the two you have edited though - could you clarify that for me please?

If I edit either one, they both get sent.

Here's the link: https://comprehensibleinputwiki.com/ciwlibrary/vidadmin.php

globalplane

Oh, maybe because only the label, and not the value, is being sent with the data to the table?

globalplane

Feel free to experiment with edits. It's not actually set up to write to the database yet.

allan

There is a problem with the label and value that I think we need to address (and I suspect resolving this will also resolve the two data parameters being sent).

Namely, consider this row:

If I click on either difficulty or language it shows not set rather than the correct value:

That is happening because there is no option in the select list that contains the value of the field.

In the data object for that row you have:

However, the difficulty options are:

                        options: [
                            { label: "* Not Yet Set", value: "0" },
                            { label: "Super Beginner", value: "1"}, 
                            { label: "Beginner", value: "2"}, 
                            { label: "Intermediate", value: "4"}, 
                            { label: "Advanced", value: "6"}, 
                        ]

I.e. there is no value which has Super Beginner. There is a label, but not a value. The item is selected by value.

Do you want to store the number (a foreign key reference?) in the database for difficulty, or the string?

Allan

globalplane

Do you want to store the number (a foreign key reference?) in the database for difficulty, or the string?

Only numbers for all editable fields.

allan

In that case, the data coming back from the server should contain that number - e.g.:

difficulty: '1',

(Note that I've used a string, since that is what your options are, but if the data is actually read from the database as a number, you should update the option values to be numbers and not strings).

You will then either want to use a custom renderer to convert the number to a string the user will understand, or include the display text in the data object - e.g.:

difficulty: '1',
difficulty_label: 'Super Beginner',

Allan

allan

This example shows how it is typically done with joined database data.

Allan

globalplane

Thank you! I'll give it a try when I get to a computer.

globalplane

" Note that I've used a string, since that is what your options are, but if the data is actually read from the database as a number, you should update the option values to be numbers and not strings"

So my options are being sent over from the server:

xxxxxx

Actually, I see it's something to do with my default label. Will investigate this myself for now.

---

Couple other quick questions:
* Is there a way to order the options in the select by value, instead of label?
* I have a field that is sometimes null. This is breaking SearchPanes, so I'm trying to set the value coming from the server as -1 when the value is null. Using render only changes the label, but leaves the value as null. How can I change the value that I send to SearchPanes?

allan

Is there a way to order the options in the select by value, instead of label?

No - the label is what the user sees, so there isn't a sort option for the value. That said, I suppose you could include the value in the label fields and then use a renderer to just display the label! See the docs here for the full range of options.

have a field that is sometimes null. This is breaking SearchPanes,

You'd need to put the renderer on SearchPanes as well. Its a pain I know. That is something I need to look at making easier!

Allan

globalplane

You'd need to put the renderer on SearchPanes as well.

I think that's what I have:

        Field::inst( 'mc_revs_enabled.value', 'enabled' )

            ->getFormatter( function ( $val, $data ) {
                return (match ($val) {
                    null => -1,
                    default => $val
                });
            } )
            ->searchPaneOptions( SearchPaneOptions::inst()
                ->render( function ( $str ) {
                    return (match ($str) {
                        null => -1,
                        default => $str
                    });

globalplane

That said, I suppose you could include the value in the label fields and then use a renderer to just display the label!

Do you mean set the options label to the database value, then rendering the option to use the database label instead? Would that not then just sort the select by the rendered label?

allan

I was actually meaning on the client-side, but yes, that should work. That said, I'm not familiar with the PHP syntax used there. render should just return a string. I'm not sure what it is returning there to be honest.

Regarding the order, thinking about it more, you should actually just be able to use the order() method of the options class - set it to order on the value column.

Allan

globalplane

This is what that code produces as a response from the server in the SearchPanes section:

{"label":-1,"total":100,"value":null,"count":100},
{"label":0,"total":9101,"value":0,"count":9101},
{"label":1,"total":2159,"value":1,"count":2159}

Which seems to go with the documentation about the render function:

SearchPaneOptions->render( function ) - A formatting function that will be applied to each label read from the database.

What is the client side code that could change the value instead of the label?

globalplane

Regarding the order, thinking about it more, you should actually just be able to use the order() method of the options class - set it to order on the value column.

order() works, thanks!

globalplane

I got the front end working correctly! I couldn't figure out how to fix it with SearchPanes, so instead I replaced null values coming from the database with -1:

Field::inst( 'IFNULL(mc_revs_level.value, -1)', 'difficulty_id')

Weirdly, having 0 as a value still made it act weird. The request when clicking on 0 in the SearchPane would look like this:
searchPanes[enabled][0]=0&searchPanes_null[enabled][0]=true
and it would return no results.

If I reran the request with searchPanes_null set to false, it would work correctly.

So I also changed all rows in the database which were 0 to 2, and it works.