How can I get the insertid from the Editor->process() method?

TomBajzek

Let me begin by saying that I've been using DataTables/Editor for several months now, and I'm very pleased by the entire software package, as well as the resulting product.

I do have a question at the moment:

My application uses an AJAX-based data source, and the server-side code simply validates the edits and then calls the process method. In the case that a new record is created, the application needs to notify certain people by email that contains that record's id. I do know that I can use a postCreate event handler to take care of this situation, but I cannot see how to obtain the insertid from the process method.

Is that possible, or do I need to replace the process method with explicit DB calls to get the insertid in this case?

Thanks in advance, and keep up the excellent work,
Tom

allan

Hi Tom,

The postCreate event handler will be passed the newly inserted row id.

The full list of events, along with the parameters passed into them is available here.

Allan

TomBajzek

Allan,

If my AJAX handler on the server just saves what is passed to it by the client, should not the conents of the $values array match the contents of the $row array? In my case, they do not. The $values array contains what the client has passed in, but the $row array does not contain the data from the record just written. Rather, it contains some other, apparently abritrary (but consistent) record in the DB. The record created in the DB contains the correct information.

My original code used the $row array to compose the email notification, whereas that data is wrong, and the $values array is correct.

I have a code segment and log entries to illustrate:

Editor::inst( $db, 'tickets', 'id' )
    ->fields(
        Field::inst( 'id' )->set(false),
        Field::inst( 'tickettime' )
            ->setValue(date("Y-m-d H:i:s"))
            ->getFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime( $val) );
                } ),
        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));
                } ),
        Field::inst( 'status' ),
        Field::inst( 'responder' )
            ->options(Options::inst()
                ->table('ITresponders')
                ->value('responder')
                ->label('responder')
                ->where( function($q) {
                    $q->where('active','yes','=');
                } )
                ->render( function ($row) {
                    return $row['responder'];
                } )
            ),
        Field::inst( 'email' ),
        Field::inst( 'category' )
            ->options(Options::inst()
                ->table('categories')
                ->value('name')
                ->label('name')
                ->render( function ($row) {
                    return $row['name'];
                } )
            ),
        Field::inst( 'description' )
    )
    ->on('postCreate', function($editor,$id,$values,$row) {
        error_log('t.t.p: Created: id= ' . $id . ', catR= ' . $row['category'] . ', descR= ' . $row['description'] . ', catV= ' . $values['category'] . ', descV= ' . $values['description']);
        notify($id,$values['category'],$values['description']);
    } )
    ->where( function ($q) {
        global $itRole,$maxAge,$theUser;
        if ($itRole != 'tech' and $itRole != 'admin') {
            $q ->where('email',$theUser,'=');   // Restrict non-(tech,admin) to own tickets
        }
        if ($maxAge != 'all') {                 // Exclude old closed tickets
//          $q ->where('updated','date_sub(now(),interval ' . $maxAge . ')','>',false);
            $q ->where('status','closed','!=')
                ->or_where(function( $r) use ($maxAge) {
                    $r->where('status','closed','=');
                    $r->where('updated','date_sub(now(),interval ' . $maxAge . ')','>',false);
                } );
        }
    } )
    ->transaction(false)
    ->process( $_POST )
    ->json();

The error_log call in the postCreate code generates the following log entry when I execute it:

2017-07-05 14:26:25 Warning 74.109.193.16 mod_fcgid: stderr: t.t.p: Created: id= 3478, catR= Unassigned, descR= Update and verify all pages of the IPCONFIG list, catV= Testing, descV= Test notification 4, referer: http://asccintranet.bajzek.com/ITTracker/itEditor.php

As you can see, the $values array and the $row array differ, and the $values array is correct. The $id variable returned is correct.

Have I found a bug, or am I not understanding something?

Also, I found that the newly-created record is not displayed after it is added by the code above. Should it necessary to refresh the page at this point?

I was going to go back to the Examples on the website to review the AJAX-sourced data example, but I see that it is no longer there. I'm sorry about that, as I had found those examples to be useful on several occasions.

Thanks for looking at this,
Tom

allan

should not the conents of the $values array match the contents of the $row array?

Generally, yes they will, but not always. The $values array is what is submitted by the client. The $row is what is read from the database - the database might have triggers to change values or formatters could have been used.

Are you saying that $row does not contain the information for the row that was just created? Is your primary key column an auto incrementing column?

Is it possible that the new row does not meet the where condition that is being applied?

Allan

TomBajzek

Allan,

The server code does nothing to the $values that are input except to save them in the DB. Thus, I fully expect the $row array to match the $values array. Yes. the primary key is an auto-incrementing column.

It was after the errors were reported to me that I inserted the error_log statement for debugging purposes and found that the $row array was not what had been submitted. The information returned in the $row array was information for another row in the table. In the failing cases on Monday, it was always the data from the same other record in the table. (It was at that point that I began to think that I had completely misunderstood the postCreate callback.)

I fixed my problem by changing the postCreate to use the $values array to generate the notification. When I installed that code yesterday afternoon, I left the debugging in. I noted two effects: first, the revised code worked and generated the correct notification; second the $row data was correct, matching the $values data. This tells me that the behavior of postCreate is inconsistent. Just now a new record was created where the $row array did not match the $values array, and it was not the same incorrect data that was being returned consistently on Monday.

I checked with the client who reported the problem on Monday. The client said that these incorrect notifications first appeared on Monday. I had made no code changes on or immediately before Monday. So far, I have no other data or evidence to help understand or explain this.

I believe that using the $values array has fixed this problem, but I'm concerned about the apparent inconsistency of behavior. I would welcome any suggestions. If there is other information that you want, please let me know.

Thanks,
Tom

allan

If you are able to tell me how to reproduce this that would be great. I've just attempted that locally here, but the $values and $row array are always what I expect in my local test cases.

Thanks,
Allan

TomBajzek

Allan,

I would love to be able to do that, but I can't do that immediately, as I have to deal with some other (unrelated) issues for this client. What's strange is that it appears that this does not happen always, so It would clearly be valuable if I can identify a pattern of success vs failures. I'll try to do that as soon as I have a chance to get back into that.

Fortunately, using the $values array seems to work all of the time, as one would expect.

Tom

allan

It is odd. Its the kind of thing that I would expect that if it worked once, it should always work.

Look forward to hearing from you.

Thanks,
Allan

TomBajzek

Allan,

I had to move on from this, but I didn't have time to create a simple example to demonstrate it. I have cases where the $values and $row are different, but these are embedded in an app this is too spread out to post to this forum.Also, the failure does not always happen, but using $values always works, as it should, so I just use that. I haven't forgotten about this issue, however, and will send you a demonstration when I get a chance.