postEdit Insert New Record, Duplicate Primary Key

vincmeister

Hi Allan,

I want to create a new record on master table tr_delivery_head by selected data from detail table tr_receiving_detail
The linked field is psm_no

My scenario is

1. User select delivery order number (using select2) [done]
2. Data will load from tr_receiving_detail based on select2 value [done]
3. Select item(s) then submit tr_receiving_detail.psm_no using Editor Edit [done]
4. Submit header data on tr_delivery_head using ->on('postEdit')

I got an error {"error":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '160298' for key 'PRIMARY'","data":[]} and i guess, the error comes from multiple selection data. My postEdit works on single data

Can editor do this?

My code

<?php
// DataTables PHP library
include( "../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
    
    if ( ! isset($_POST['dom_no']) || ! is_numeric($_POST['dom_no']) ) {
        echo json_encode( [ "data" => [] ] );
    }
    else {
        Editor::inst( $db, 'tr_receiving_detail' )
            ->pkey('tr_receiving_detail.id')
            ->fields(
                Field::inst( 'tr_receiving_head.tr_in_id' ),
                Field::inst( 'tr_receiving_detail.id' ),
                Field::inst( 'tr_receiving_detail.item_code' )
                    ->options( 'master_item', 'item_code', 'item_name'),
                Field::inst( 'master_item.item_name' ),
                Field::inst( 'tr_receiving_detail.quantity' ),
                Field::inst( 'tr_receiving_detail.customer_id' )
                    ->options( 'master_customer', 'customer_code', 'customer_name' ),
                Field::inst( 'master_customer.customer_name' ),
                Field::inst( 'tr_receiving_detail.enduser_code' ),
                Field::inst( 'tr_receiving_detail.enduser_name' ),
                Field::inst( 'tr_receiving_detail.reference' ),
                Field::inst( 'tr_receiving_detail.changes' ),
                Field::inst( 'tr_receiving_detail.dom_no' ),
                Field::inst( 'tr_receiving_detail.old_id' ),
                Field::inst( 'tr_receiving_detail.tr_in_id' ),
                Field::inst( 'tr_receiving_detail.psm_no' ),
                Field::inst( 'tr_receiving_head.tr_return' ),
                Field::inst( 'tr_delivery_head.psm_no' ),
                Field::inst( 'tr_delivery_head.psm_date' ),
                Field::inst( 'tr_delivery_head.customer_code' ),
                Field::inst( 'tr_delivery_head.customer_code' ),
                Field::inst( 'tr_delivery_head.expedition_code' )
                    ->options( 'master_expedition', 'expedition_code', 'expedition_name' ),
                Field::inst( 'master_expedition.expedition_name' ),
                Field::inst( 'tr_delivery_head.plate_no' ),
                Field::inst( 'tr_delivery_head.container_no' ),
                Field::inst( 'tr_delivery_head.notes' )
            )
            ->leftJoin( 'tr_receiving_head', 'tr_receiving_head.tr_in_id', '=', 'tr_receiving_detail.tr_in_id' )
            ->leftJoin( 'master_item', 'master_item.item_code', '=', 'tr_receiving_detail.item_code' )
            ->leftJoin( 'master_customer', 'master_customer.customer_code', '=', 'tr_receiving_detail.customer_id' )
            ->leftJoin( 'tr_delivery_head', 'tr_delivery_head.psm_no', '=', 'tr_receiving_detail.psm_no' )
            ->leftJoin( 'master_expedition', 'master_expedition.expedition_code', '=', 'tr_delivery_head.expedition_code' )
            
            ->where ('tr_receiving_detail.psm_no','')
            ->where ('tr_receiving_detail.changes','')
            ->where ('tr_receiving_detail.dom_no',0, '>')
            ->where ( 'tr_receiving_head.tr_return', 'No', '=' )
            ->where( 'tr_receiving_detail.dom_no', $_POST['dom_no'] )
        
        ->on('postCreate',function( $editor, $id, $values, $row ) {
            $editor->db()
                ->query('update', 'tr_receiving_detail')
                ->set('tr_receiving_detail.changes',1)
                ->where('tr_receiving_detail.id', $values['tr_receiving_detail']['old_id'])
                ->exec();
            })
        
        ->on('postCreate',function( $editor, $id, $values, $row ) {
            $editor->db()
                ->query('insert', 'tr_receiving_detail')
                ->set('tr_receiving_detail.item_code', $values['tr_receiving_detail']['item_code'])
                ->set('tr_receiving_detail.quantity', $_POST['q2'])
                ->set('tr_receiving_detail.customer_id', $values['tr_receiving_detail']['customer_id'])
                ->set('tr_receiving_detail.enduser_code', $values['tr_receiving_detail']['enduser_code'])
                ->set('tr_receiving_detail.enduser_name', $values['tr_receiving_detail']['enduser_name'])
                ->set('tr_receiving_detail.reference', $values['tr_receiving_detail']['reference'])
                ->set('tr_receiving_detail.changes', $values['tr_receiving_detail']['changes'])
                ->set('tr_receiving_detail.dom_no', $values['tr_receiving_detail']['dom_no'])
                ->set('tr_receiving_detail.old_id', $values['tr_receiving_detail']['old_id'])
                ->set('tr_receiving_detail.tr_in_id', $values['tr_receiving_detail']['tr_in_id'])
                ->exec();
            })
        
        ->on('postCreate',function( $editor, $id, $values, $row ) {
            $editor->db()
                ->query('update', 'tr_receiving_detail')
                ->set('tr_receiving_detail.changes',1)
                ->where('tr_receiving_detail.id', $values['tr_receiving_detail']['old_id'])
                ->exec();
            })
            
        // edit then create, problem di primary key. only works on single row
        ->on('postEdit',function( $editor, $id, $values, $row ) {
            $editor->db()
                ->query('insert', 'tr_delivery_head')
                ->set('tr_delivery_head.psm_no', $values['tr_receiving_detail']['psm_no'])
                ->set('tr_delivery_head.psm_date', $values['psm_date'])
                ->set('tr_delivery_head.customer_code', $values['tr_receiving_detail']['customer_id'])
                ->set('tr_delivery_head.expedition_code', $values['expedition_code'])
                //->set('tr_delivery_head.expedition_code', $values['tr_delivery_head']['expedition_code'])
                ->set('tr_delivery_head.plate_no', $values['plate_no'])
                ->set('tr_delivery_head.container_no', $values['container_no'])
                ->set('tr_delivery_head.notes', $values['notes'])
                ->exec();
            }) 
        
            ->process($_POST)
            ->json();
    }
?>

Here's my test link please advise

allan

The error you are seeing means that you are attempting to create a new row that has a primary key value that matches an existing row's primary key value. That obviously isn't valid.

You would need to duplicate everything apart from the primary key value.

Allan

vincmeister

You would need to duplicate everything apart from the primary key value.

I don't understand how to do that, because my scenario is Update row on detail table then create row on master table

i'm trying to create new row with this postEdit

->on('postEdit',function( $editor, $id, $values, $row ) {
            $editor->db()
                ->query('insert', 'tr_delivery_head')
                ->set('tr_delivery_head.psm_no', $values['tr_receiving_detail']['psm_no'])
                ->set('tr_delivery_head.psm_date', $values['psm_date'])
                ->set('tr_delivery_head.customer_code', $values['tr_receiving_detail']['customer_id'])
                ->set('tr_delivery_head.expedition_code', $values['expedition_code'])
                //->set('tr_delivery_head.expedition_code', $values['tr_delivery_head']['expedition_code'])
                ->set('tr_delivery_head.plate_no', $values['plate_no'])
                ->set('tr_delivery_head.container_no', $values['container_no'])
                ->set('tr_delivery_head.notes', $values['notes'])
                ->exec();
            })

The primary is tr_delivery_head.psm_no and the value is set from $values['tr_receiving_detail']['psm_no'] which populate automatic from Editor Edit.

If i select multiple rows, then update the tr_receiving_detail.psm_no' , the Edit function works perfectly. But the postEdit goes error. I guess because the postEdit also runs multiple time, same as the number of selected row. CMIIW.

If that correct, i think my scenario will not working, except, i can run the postEdit one time only.

Please advise, thank you
Danny

allan

Can you not just remove the set of the psm_no parameter? If its a primary key you certainly can't set it to the same value as an existing row. Is it not an auto increment field? Can you not just let the database fill it in?

Allan

vincmeister

hello Allan,

Following your suggestion to let the MySQL fill psm_no, the code is working, but the result is not as expected. It create multiple records on database, same as the selected rows number. If i select 2 rows, table tr_delivery_head also create 2 rows, I only need 1 row, as header

My requirement is, the postEdit only calls once, whatever the selected rows number on tr_delivery_detail . Is it possible to do that?

Please advise, thank you
Danny

allan

If i select 2 rows, table tr_delivery_head also create 2 rows

That's what I would have expected from the above code. The postEdit event will be triggered for every row that is edited.

My requirement is, the postEdit only calls once, whatever the selected rows number on tr_delivery_detail . Is it possible to do that?

Not with postEdit. You would need to do something like:

if ( Editor::action( $_POST ) === Editor::ACTION_EDIT ) {
  ...
}

After the request has been processed.

Allan

vincmeister

Hi Allan,

I need more help.

After the request has been processed.

So I assume i must put the code after the

->process($_POST)
->json();

My plan is get the field's value to php variable, then store it to MySQL
I try this

if ( Editor::action( $_POST ) === Editor::ACTION_EDIT ) {
$psm_no = $_POST['tr_receiving_detail']['psm_no'];
$plate_no = $_POST['tr_delivery_head']['plate_no'];
}

but i got error

Undefined index: tr_receiving_detail
Undefined index: tr_delivery_head

Or i can use the Editor Instance maybe something like this, because the value comes from user input

Field::inst( 'tr_delivery_head.psm_no' ),
Field::inst( 'tr_delivery_head.plate_no' )
->process($_POST)
->json();

Please help, thank you

allan

The data structure that Editor sends to the server is not quite like how you have used it. The data structure is documented here and if you read over it you will see that it submits the primary key as part of the row.

So you might use:

$psm_no = $_POST['ROW-IDENTIFIER']['tr_receiving_detail']['psm_no'];

where obviously ROW-IDENTIFIER would be whatever the row identifier is. You'll likely need to use a foreach loop to do that.

Allan