Editor PHP

menashe · April 29

You have a discussion of the working of the Editor PHP libraries at https://editor.datatables.net/manual/php/getting-started

For several years, I have thought of using straight SQL, but I have not found a comprehensive discussion of how it might be done. For example, exactly how do I pass the (four??) required parameters--beginning record, number of records, etc.

Are there any pointers that you can share? Links?

Thanks!

allan · April 29

When you say "straight SQL" - you'll need to explain that a little more I'm afraid. The client-side doesn't submit SQL for the server to execute - that would be a horrendous security hole.

I'm going to assume you have a an environment at the server-side, such as PHP, and want to take the data submitted from the client-side, build your own SQL statements based on that (make sure you do validation as well!) and execute it.

Based on that assumption, take a look at this page. It documents the client / server data interchange that Editor uses and with the information there you will be able to implement your own server-side code.

Happy to answer any questions you might have about it.

Allan

menashe · April 29

Hi Allan,

Thank you! That is what I was looking for. It is probably a lot of work; who knows if I'll get to it.

allan · April 29

It can be simple for specific use cases - if you know what is coming in, it is easy to expect those variables. It is when you start handling dynamic options (e.g. multiple different forms) that things start to get more interesting!

Allan

rf1234 · April 30

Interesting discussion, guys!

I have been replacing Editor's logic with my own SQL as well: But only partly, not completely! I wanted more flexibility without giving up on the advantages that Editor has.

Some use cases:

Making my log table user accessible I use Editor in read only mode, get the fields to be displayed by json_decoding the log entry and also massaging the result returned from the server by embedding my own SQL on "postGet" for example.

I also have quite many occasions where I have to return the format of an Mjoin from the server but the SQL behind it is much more complex than Editor's Mjoin method can handle. Occasionally I also have to make much more complex updates than Editor can handle and use my own SQL for that as well. I do this on "validatedEdit", "validatedCreate", "writeEdit", "writeCreate" etc.

@menashe
If you need examples for such a "hybrid" approach, just let me know.

Roland

menashe · April 30

Roland,

Thank you! I would love to see examples.

I am very proficient in SQL, but I am "stuck" on how it would be "wrapped" to send the required information to the server. Any help is greatly appreciated. **

rf1234 · April 30

No problem. I can share something.

If you want to use your own SQL it is critical to find the right "hooks" i.e. event handlers, getFormatters etc., to insert the SQL.

Here is an example that is part of my solution to implement soft deletion. I think @allan supports this by now; so this is my own solution that I built without knowing about Allan's solution for this. But it should only serve as an example on how to insert your own SQL, I guess.

Soft deletion is actually editing, not removing rows. Hence the event handlers on the server are "validatedEdit", "writeEdit" and "postEdit" (and for completenes "writeCreate").

This is what I do:
"validatedEdit"
I find out whether the user attempts to delete any files by comparing them with what is saved in the database. File deletions may not be done physical but only logical with soft deletion. But: Editor will delete the link table to the file when a user deletes it. I need to mitigate that.

I use a SELECT statement to read the database using Editor's "raw" method. But you could also use your own db-handler for this.
Eventually I save any deleted files in a session variable $_SESSION['deletedFiles'] because I can't pass them to the next event handler. But no problem to use a session variable.

Then I make another SELECT to figure out whether this is a recovery situation (i.e. the undoing of a soft deletion) etc..

On "postUpload" and "postCreate" I only write the log using a helper function that also uses SQL. In this case it uses Editor's "insert" method but of course you could use plain SQL as well using the "raw" method or using your own db-handler.

On "writeCreate" and "writeEdit" I make additional manipulations using SQL helper functions before the data is read back from the server.

Then on "writeEdit" (after making the db-updates but before data is being read back from the server) I make additional db-updates so that Editor reads back the stuff I want it to read from the server. Here I use "raw" SQL but also Editor's "insert" and "update" methods. At the bottom you see "raw" method code commented out. This code would do the same as Editor's "update" method above it.

This is the code. I also added the helper functions called below. It is not particularly "elegant" but it can show you what you can do using those Editor event handlers.

->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {

    $_SESSION['deletedFiles'] = [];
    $_SESSION['recovery'] = 0;   

    //if comment or description: do nothing!
    if ( ! isset($values['file-many-count']) ) {
        return;
    }

    //get all the submitted file ids into a simple array
    $clientFiles = [];
    if ( $values['file-many-count'] > 0 ) {
        $clientFiles = array_column($values['file'], 'id');
        sort($clientFiles);
    }
    //read all file_ids of the contract
    $statement = ('SELECT file_id FROM ctr_has_file   
                    WHERE ctr_id = :ctr_id');  
    $result = $db->raw()
                 ->bind(':ctr_id', $id)
                 ->exec($statement);
    $row = $result->fetchAll(PDO::FETCH_ASSOC);
    $dbFiles = [];
    if ( (bool)$row ) {
        $dbFiles = array_column($row, 'file_id');
        sort($dbFiles);
    }
    //get files in db but not in client submission
    //array_diff returns the values from the first array that
    //are not contained in the second
    //if the result is empty nothing was deleted!
    $deletedFiles = array_diff( $dbFiles, $clientFiles ); 
    if ( $deletedFiles != [] && $deletedFiles != null ) {
        $_SESSION['deletedFiles'] = $deletedFiles;
    }

    //check whether we have a contract recovery situation:     
    if ( ! (bool)$values['ctr']['soft_deleted'] ) {
        //read all file_ids of the contract
        $statement = ('SELECT soft_deleted FROM ctr   
                        WHERE id = :ctr_id');  
        $result = $db->raw()
                    ->bind(':ctr_id', $id)
                    ->exec($statement);
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$row["soft_deleted"] ) {
            $_SESSION['recovery'] = 1;
        }
    }
} )
->on( 'postUpload', function ( $editor, $id, $files, $data ) {
    logChangeCtrUpload( $editor->db(), $id );
} )
->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
    logChange( $editor->db(), 'create', $id, $row, 'ctr' );
} )
->on('writeCreate', function ( $editor, $id, $values ) use ( $db ) {
    processCtrLinkedContracts ( $values, $id, $db );
    processCtrHasLeadingCtrGovdept( $values, $id, $db );
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
    processCtrLinkedContracts ( $values, $id, $db );
    processCtrHasLeadingCtrGovdept( $values, $id, $db );
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
// we reinsert the deleted link table record and update the file table
// entry to be soft_deleted
    foreach ( $_SESSION['deletedFiles'] as $delId ) {            
        //re-insert link table between ctr and file
        $res = $db->insert( 'ctr_has_file', array (
            'ctr_id'    => $id,
            'file_id'   => $delId
        ) );
        $deletionPermitted = true;
        //these readers may only delete their own files!!
        if ( isset($_SESSION['readersMayUploadDocs']) ) {
            $statement = ('SELECT creator_id FROM `file` WHERE id = :delId');  
            $result = $db->raw()
                         ->bind(':delId', $delId)
                         ->exec($statement);
            $rw = $result->fetch(PDO::FETCH_ASSOC);
            if ( $rw["creator_id"] != $_SESSION['id'] ) {
                $deletionPermitted = false;
            }
        }
        if ( $deletionPermitted ) {
            // update the file record to be soft_deleted
            $res = $db->update( 'file', array( 
                'soft_deleted'  => 1,
                'creator_id'    => $_SESSION['orig_id']             
            ), array( 'id' => $delId ) );
        }
//            $db->raw()
//               ->bind( ':softDeleted',  1 ) 
//               ->bind( ':creatorId',    $_SESSION['id'] )
//               ->bind( ':fileId',       $delId )
//               ->exec( 'UPDATE `file` 
//                           SET `soft_deleted`  = :softDeleted,
//                               `creator_id`    = :creatorId 
//                         WHERE `id`            = :fileId' );

    }
    $_SESSION['deletedFiles'] = [];
} )

And the helper functions:

function logChange ( $db, $action, $id, $values, $tableChanged ) {
    $db->insert( 'log', array(
        'user_id'       => $_SESSION['orig_id'],
        'table'         => $tableChanged,
        'action'        => $action,
        'values'        => json_encode( $values ),
        'changed_id'    => $id
    ) );
}

function logChangeCtrUpload ( $db, $id ) {
    $db->insert( 'ctr_upload', array(
        'user_id'       => $_SESSION['orig_id'],
        'file_id'       => $id
    ) );
}

function processCtrLinkedContracts ( $values, $id, $db ) {
    //if comment or description: do nothing!
    if ( ! isset($values['ctr_has_ctr-many-count']) ) {
        return;
    }
    $db->raw()
       ->bind( ':id', $id )
       ->exec( 'DELETE FROM ctr_has_ctr
                 WHERE ctr_id        = :id
                    OR linked_ctr_id = :id' );

    for ($i = 0; $i < $values['ctr_has_ctr-many-count']; $i++) {
        $linkedId = $values["ctr_has_ctr"][$i]["linked_ctr_id"];
        $res = $db->insert( 'ctr_has_ctr', array (
            'ctr_id'        => $id,
            'linked_ctr_id' => $linkedId
        ) );
        $res = $db->insert( 'ctr_has_ctr', array (
            'ctr_id'        => $linkedId,
            'linked_ctr_id' => $id
        ) );
    }
}

function processCtrHasLeadingCtrGovdept( $values, $id, $db ) {
    
    $subReg = "ctr";
    if ( isset($_SESSION['subRegAnyPage']) ) {
        $subReg = "sub";
    }
    
    //if the leading ctr govdept is not submitted: do nothing
    if ( ! isset($values[$subReg]["leading_ctr_govdept_id"]) ) {
        return;
    }
    $db->raw()
       ->bind( ':ctr_id', $id )
       ->exec( 'DELETE FROM ctr_has_leading_ctr_govdept
                 WHERE ctr_id = :ctr_id' );
    
    if ( $values[$subReg]["leading_ctr_govdept_id"] > 0 ) {
        $res = $db->insert( 'ctr_has_leading_ctr_govdept', array (
            'ctr_id'            => $id,
            'ctr_govdept_id'    => $values[$subReg]["leading_ctr_govdept_id"]
        ) );
    }
}

rf1234 · April 30

Here is a getFormatter returning the same format as an Mjoin. As you can see I alias the column to pass in the value I need for selection and return the result under a different name in the "Mjoin"-format. Here I use my own db-handler and invoke it using "global".

Field::inst( 'ctr_govdept.id AS gov' )->set( false )   //return same format as an MJoin             
    ->getFormatter( function($val, $data, $opts) {
        return getFormatterGovArray($val);
    }),

function getFormatterGovArray($ctrGovdeptId) {
    global $dbh;

    $dbh->query('SELECT DISTINCT a.name AS govName, a.regional_12 AS govRegional12
                   FROM gov a
             INNER JOIN ctr_installation_has_gov b          ON a.id = b.gov_id
             INNER JOIN ctr_installation c                  ON b.ctr_installation_id = c.id
             INNER JOIN ctr_govdept_has_ctr_installation d  ON c.id = d.ctr_installation_id
                  WHERE d.ctr_govdept_id = :ctrGovdeptId
               ORDER BY 1 ASC');
    $dbh->bind(':ctrGovdeptId', $ctrGovdeptId); 

    return $dbh->resultsetAssoc();     
}

Editor PHP

Editor PHP

Answers

Howdy, Stranger!

Categories

DataTables

Editor PHP

Editor PHP

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables