using postEdit to update main table fails

YoDavish

I have editor v 1.9.6.
Within the postEdit fucntion on my server side PHP page, there is a logChange function that will save the old and new value for an audit log onto a new table, this works perfectly fine. However, I want to add an additional function to perform updates to the "Task" table depending on 2 column values. For instance,

If "TaskDefId" column = "2" and "Status" column = "complete"
it would call a new function "handleTaskStatusChange" to update the Task table to "TaskDefId" column = "8" and "Status" column = "created", however when I run this, DataTables stalls and does not update "TaskDefId" = "8". If debug the sql and parameters, everything looks correct for the update and putting in a file_put_contents() I see it reaches the function correctly. Additionally, if I comment out "executeSql" with in the "updateTaskTable" function, then it runs smoothly again. I'm assuming it's a timing or synchronization thing for why this fails to update? How can I call this update in handleTaskStatusChange function, if not in the "postEdit" to be performed? Code is below:

```php
<?php
session_start();
// Task Workflow Handler
include("taskWorkflowHandler.php");

// DataTables PHP library
include ("../lib/datatables.inc.php");

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

$prevValues = [];
// runs only one per row

function getPrevValues( $db, $table, $id ){
global $prevValues;
$prevValues = $db->select( $table, '*', [ 'id' => $id ] )->fetch();
}

function logChange( $db, $table, $action, $id, $values) {
date_default_timezone_set('America/Chicago');
global $prevValues;
switch ($action) {
case "create":
$oldValues = [];
$newValues = $values;
break;
case "edit":
$oldValues = array_intersect_key(array_diff_assoc($prevValues,$values[$table]),array_diff_assoc($values[$table],$prevValues));
$newValues = array_intersect_key(array_diff_assoc($values[$table],$prevValues),array_diff_assoc($prevValues,$values[$table]));
break;
case "delete":
$oldValues = $prevValues;
$newValues = [];
break;
}

if (!empty($oldValues) || !empty($newValues)){
    foreach($oldValues as $oKey => $oValue){
        foreach($newValues as $nKey => $nValue){
            if($oKey == $nKey){ // only insert if the keys match
                $db->insert( 'taskaudit', array(
                    'user'      => isset($_SESSION['user']), 
                    'action'    => $action,
                    'oldValue'  => $oValue,
                    'newValue'  => $nValue,
                    'table'     => $table,
                    'rowId'     => $id,
                    'column'    => $oKey,
                    'date'      => date('Y-m-d H:i:s')
                ));
            }
        }
    }
    if(array_key_exists('statusDefId', $newValues) && $action == 'edit'){
        handleTaskStatusChange($oldValues, $newValues, $prevValues, $id);
    }
}

}

// Build our Editor instance and process the data coming from POST
$editor = Editor::inst( $db, 'task', 'id');
$tableServerFieldApplicationId = 'tableServerFields'."".$_SESSION['applicationPageId'];
if(isset($_SESSION[$tableServerFieldApplicationId])){
foreach($_SESSION[$tableServerFieldApplicationId] as $fieldRow){
$editor->fields(Field::inst($fieldRow));
}
}

// Pre functions
// preEdit:  Pre-row update event - triggered before the row / page data is updated.
$editor->on( 'preEdit', function ( $editor, $id, $values){
    getPrevValues($editor->db(), $editor->table()[0], $id);
 });
$editor->on( 'preRemove', function ( $editor, $id, $values){
    getPrevValues($editor->db(), $editor->table()[0], $id);
 });
//Post functions
// postEdit:  Post-row edit event - triggered after the row / page has been updated.
$editor->on( 'postEdit', function ( $editor, $id, $values, $row ){
    logChange( $editor->db(), $editor->table()[0], 'edit', $id, $values );
 });
$editor->process( $_POST );
$editor->json();

// taskWorkflowHandler.php page:
$connectPdoResults = connectPdo();

function handleTaskStatusChange($oldValue, $newValue, $taskData, $taskId){
global $connectPdoResults;
$errorCode = "";
$errMsg = "";

// get taskWorkflow details
$taskWorkflowResult = getTaskWorkflow($connectPdoResults['pdo'], $taskData, $newValue['statusDefId']);
$twfRow = $taskWorkflowResult['data'];

if($taskWorkflowResult['errorCode']){
    $errorCode = $taskWorkflowResult['errorCode'];
    $errMsg .= $taskWorkflowResult['errMsg'];
}
// If createNewTask is 0
if($twfRow['createNewTask'] == 0){
    $updateTaskResult = updateTaskTable($connectPdoResults['pdo'], $twfRow, $taskId);
    if($updateTaskResult['errorCode']){
        $errorCode = $updateTaskResult['errorCode'];
        $errMsg .= $updateTaskResult['errMsg'];
    }
}

}

function updateTaskTable($pdo, $taskworkflowData, $taskId){
$taskArgs = [];
$errorCode = "";
$errMsg = "";

$updateTaskSql = 'update prosystem_dev.task set taskDefId = ? ';
$taskArgs[] = $taskworkflowData['nextTaskDefId'];
if($taskworkflowData['nextStatusDefId']){
    $updateTaskSql .= ', statusDefId = ? ';
    $taskArgs[] = $taskworkflowData['nextStatusDefId'];
}
$updateTaskSql .= ' where id = ?';
$taskArgs[] = $taskId;
$updateTaskResult = executeSql($pdo, $updateTaskSql, $taskArgs);
if($updateTaskResult['errorCode']){
    $errorCode = $updateTaskResult['errorCode'];
    $errMsg = $updateTaskResult['errMsg'];
}
// return if errors exist
return ['errorCode'=>$errorCode, 'errMsg'=>$errMsg];

}

<?php > ``` ?>

allan

I'm assuming it's a timing or synchronization thing for why this fails to update?

It will be a locking issue. Editor's PHP libraries operate inside a transaction so that if anything goes wrong, then the db tables will just rollback to the state they were in before. That means that you can't read the changes until the transaction is committed unless you are in the same transaction.

So - two options:

1. Use Editor->transaction(false) to disable transactions, or
2. Use Editor->db() to get the current database transaction object and reuse that.

Allan

YoDavish

Thanks @allan , I added the "$editor->transaction(false);" right before the "$editor->process( $_POST );" and that solved the issue!