Problems using PHP events

TomBajzek

I'm having problems using the server-side postEdit event (in PHP). In the first case, I'm using the postCreate event for a row in one table to insert a new record into each of three other tables in the same database. The event handler calls the function insertNewUserRecords that writes the records in the other tables. This works just fine. The code excerpt is here:

// Here we need to insert a new record into the related tables when creting a new user
function insertNewUserRecords($db, $row) {
    $db->insert('adminGroups', array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
    $db->insert('docViewGroups', array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
    $db->insert('preferences', array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
}

// Here we update the email field when a record is edited
function updateUserRecords($db, $id, $row) {
    $db->edit('adminGroups', array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
    $db->edit('docViewGroups',  array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
    $db->edit('preferences', array(
        'email' => $row['email'],
        'updated' => date('Y-m-d H:i:s')
    ) );
}

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'roles', 'id' )
    ->fields(
        Field::inst( 'email' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::minLen( 2 ) ),
        Field::inst( 'active' )
            ->validator( Validate::notEmpty() ),
// ...Omitted rows of fields here...
        Field::inst( 'updated' )
            ->setValue(date("Y-m-d H:i:s"))
            ->getFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime( $val) );
            } )
            ->setFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime($val));        
            } )
    )
    ->on( 'postCreate', function( $editor, $id, $values, $row ) {
        // Now create a new record in adminGroups, docViewGroups, and preferences
        insertNewUserRecords($editor->db(), $values);
    } )
    ->on( 'postEdit', function( $editor, $id, $values, $row ) {
        // Now update the corresponding records in adminGroups, docViewGroups, and preferences
        updateUserRecords($editor->db(), $id, $values);
    } )
    ->process( $_POST )
    ->json();

I have a postEdit handler that wants to update the email field in the other three tables if the user edits that field in the roles table. This is where the problems arise.

First of all, the call to updateUserRecords in the postEdit handler fails, getting Fatal error: Uncaught Error: Call to undefined method DataTables\Database::edit() in the function declaration(line 19). I don't see why this should happen, as the insert method is not undefined in the prior function and the structures appear to be parallel.

Second, I'm not sure how I should be using $id **there, as the example in the documentation does not show an example of an **edit call.

Third, $id is not valid in the other tables, as they have been constructed differently over time, and a person might not have the same id in each of the other tables. However, in the other tables, the email field is unique, and a key (but not primary), and I'd like to be able to update the other tables based on the email field. I don't see how to do that.

Can you provide some guidance here, please?

Thanks,
Tom

allan

Hi Tom,

Uncaught Error: Call to undefined method DataTables\Database::edit()

There is no edit method on the Database class. The API reference for that class is here.

Perhaps you should be using the update method?

However, in the other tables, the email field is unique, and a key (but not primary), and I'd like to be able to update the other tables based on the email field.

Use $values['email'] to get the value submitted for that field in the event handler. However, you'll need to know the previous value as well if you are using that as your primary key, so you can select and then update it. There are two ways to do that:

1. Use the preEdit event handler to query the database to get the current value, or
2. Add a hidden field which will duplicate the value of the original e-mail address, so it will also be submitted to the server. I feel this is more likely to go wrong though, and as the pkey for your records, that could be really bad.

Allan

TomBajzek

Allan,

Thanks for the pointer to the update method. I had in mind the client-side events where there is an edit method for the editor, and I missed the update in the PHP library.

I see why the preEdit is the way to carry along the old value of the key. I appreciate that tip as well.

Thanks,
Tom

TomBajzek

Hi Allan,

Now that I'm getting around to doing this, I realize that I don't see how to get the original value of the email field in the preEdit event. Do I need to try to extract it from the $editor instance, or is there a more straightforward way that I'm not seeing?

Thanks,
Tom

allan

You’ll need to query the database to get it. Use the row id that is being edited to get the current value.

Allan

TomBajzek

Hi Allan,

I'm trying to follow your suggestion of using a query to extract the original email field from the row to be edited. I'm getting an exception in PHP for Array to String Conversion in MysqlQuery.php:96 when I try to execute the $db->update.

The failure occurs in this function:

function updateUserRecords($db, $newEmail) {
    global $theEmail;
    global $db;
    $db->update('adminGroups', 
        ['email',$newEmail],
        ['email',$theEmail,'=']
    );
    $db->update('docViewGroups',  array(
        array('email',$newEmail),
        array('email',$theEmail,'=')
    ) );
    $db->update('preferences', array(
        array('email',$newEmail),
        array('email',$theEmail,'=')
    ) );
}

The original value of the email field is saved here:

    ->on( 'preEdit', function( $editor, $id, $values ) use( $db ) {
        // Save the email to be used as the key for updating the other tables
        global $theEmail;
        global $db;
        $theEmail = $editor
            ->db()
            ->query( 'select' )
            ->get( 'email' )
            ->table( 'roles' )
            ->where( 'id', $id, '=' )
            ->exec()
            ->fetchAll();
    } )

The call of the failing function is here:

    ->on( 'postEdit', function( $editor, $id, $values, $row ) {
        // Now update the corresponding records in the other tables
        global $theEmail;
        global $db;
        // updateUserRecords($editor->db(), $id, $values['email']);
        updateUserRecords($editor->db(), $values['email']);
    } )

My breakpoint in the failing routine appear shows what appear to be the correct values for $theEmail and $newEmail, but somehow the type is wrong inside the library. I do not understand why this is happening.

Tom

tangerine

fetchAll() returns an array; you are trying to use its result as a string.

TomBajzek

That helped, but I'm still having a problem here that I can't track down: When I run this, I get a PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause' in line 6 of the first code excerpt.

I'm trying to update the record in the adminGroups table whose email field matches the value of the email field I saved in the preEdit event.

I've updated that code segment as shown here:

function updateUserRecords($db, $newEmail) {
    global $theEmail;
    global $db;
    $db->update('adminGroups', 
        ['email',$newEmail],
        ['email',$theEmail,'=']
    );
    $db->update('docViewGroups',
        ['email',$newEmail],
        ['email',$theEmail,'=']
    );
    $db->update('preferences',
        ['email',$newEmail],
        ['email',$theEmail,'=']
    );
}

The preEdit event handler has been changed slightly:

    ->on( 'preEdit', function( $editor, $id, $values ) use( $db ) {
        // Save the email to be used as the key for updating the other tables
        global $theEmail;
        global $db;
        $oldEmail = $editor
            ->db()
            ->query( 'select' )
            ->get( 'email' )
            ->table( 'roles' )
            ->where( 'id', $id, '=' )
            ->exec()
            ->fetchAll();
        $theEmail = array_pop($oldEmail[0]);
    } )

I would greatly appreciate further insight into this SQL error. I'm stuck at this point.

Thanks,
Tom

TomBajzek

I should slightly amend my previous post, as the error occurs inside DataTables at MysqlQuery.php:105. The stack trace points back to line 6 in the code segment I posted above.

Tom

allan

['email',$theEmail,'=']

I think should just be:

['email' => $theEmail]

Allan

TomBajzek

Hi Allan,

That suggestion fixed it! I was banging my head against the wall because I hadn't seen what the real problem was.

Thanks very much!
Tom