How update field value of parent table with a field value in child table

How update field value of parent table with a field value in child table

GargiucnGargiucn Posts: 104Questions: 29Answers: 0

I have a parent table called "contacts" and a child table called "visits".
For each contact I can have multiple visits.
When, at a certain visit, the contact decides to sign a contract, I need to change a value of a parent table "contacts" field from 0 to 1 to indicate that the contact has become a customer.
I do not know how to do...

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    row().data() or cell().data() is one way to do this if you have the information for that specific row / cell available (e.g. via a web socket subscription).

    Another option, which I use in this post is to call ajax.reload() on the parent table when the child table is updated.

    Allan

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    I try to explain myself better.
    My problem is to change the value of a field in the "contacts" table when I save a row in the "visits" table.
    The "contacts" table contains all the names: by selecting a row the "visits" table shows only the visits made to that name.
    The relationship between the two tables works perfectly.
    But now I would like the table "contacts" to show me only the names that have at least one visit hiding all the others.
    In practice, I would like a certain "contact" to become "customer" when, in the "visits" table, I select an item such as "signed contract".
    At this point after saving the visit I should change the value of a field in the "contacts" table from 0 to 1 to make it "customer".
    But I am in the php on the table "visits" and I do not know how to do ...

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Thanks for the additional information. The easiest option is to listen for submitComplete on your visits table - when that event is called, use ajax.reload() to reload the content of the contacts table.

    Allan

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    My question is similar to that of bpitoni:
    https://datatables.net/forums/discussion/comment/126456/#Comment_126456
    I try to summarize the core of the problem and I apologize again for the lack of clarity.
    I have a "visits" table linked to the "contacts" table through a "leftjoin".
    When I add a new row in the "visits" table I can select the contacts name and fill in all other fields.
    But, when I save the record of "visits" I would like the script could also modify a field in the table "contacts", e.g. from 0 (contacts without visits) to 1 (contacts with at least one visit)

    <?php
    session_start();
    include( "DataTables.php" );
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    Editor::inst( $db, 'visits.visit_id' )
        ->fields(
            Field::inst( 'contacts.cont_visits' ),
            Field::inst( 'visits.visit_user' )->set( Field::SET_CREATE ),   
            Field::inst( 'visits.visit_date' ),
            Field::inst( 'visits.visit_idcont' )
                ->options( Options::inst()
                    ->table( 'contacts' )
                                   ->value( 'cont_id' )
                                   ->label( 'cont_name' ) 
                      ->where( function ($q) {
                        $q->where( 'cont_user', $_SESSION['id'], '=' );
                    } )
                )
                ->validator( Validate::dbValues() ),                
            Field::inst( 'contacts.cont_name' ),            
            Field::inst( 'visits.visit_note' )
        )
        ->on( 'preCreate', function ( $editor, $values ) {
            $editor
                ->field( 'visits.visit_user' )
                ->setValue( $_SESSION['id'] );
        } ) 
    
           /********** beginning of my stupid attempts **********/
    
        ->on( 'postCreate', function ( $editor, $values ) {
            $editor
                ->field( 'contacts.cont_visits' )
                ->setValue( 1 );
        } ) 
        ->on( 'postEdit', function ( $editor, $values ) {
            $editor
                ->field( 'contacts.cont_visits' )
                ->setValue( 1 );
        } ) 
    
           /********** end of my stupid attempts **********/
    
        ->leftJoin( contacts', cont_id', '=', 'visits.visit_idcont' )
        ->where( function ($q) {
            if ( ! isset($_POST['contacts']) || ! is_numeric($_POST['contacts']) ) {
                $q
                    ->where( 'cont_user', $_SESSION['id'], '=' );
        
                                    /******* another stupid attempt ********/
                                    // show only contacts that have at least one visit
                                    //->and_where( function ( $r ) {
                    //  $r->where( 'cont_visits', '1' );
                                    //} );
            }else{
                $q->where( contacts.cont_id', $_POST['contatti'] );
            }
        } )
        ->process( $_POST )
        ->json();
    ?>
        
    

    Thank you again for your patience ...

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    Hi,

    This is something that needs to be resolved in the Javascript (client-side) rather than the PHP (server-side).

    have a read over this section in the blog post that I linked to earlier. That shows how ajax.reload() can be used to refresh the data in the parent table.

    Allan

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    I want to write a different value in the parent table when I save data in the child table.
    For this I try to understand how I can do it because I am on the server side of the child table.
    I use ajax.reload () and the example you indicated and everything works very well between the two tables.
    But I do not have to update the table display, I have to change a field value in the database.
    Thanks anyway for the patience and sorry if maybe I did not understand...

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    A server-side event is the way to do it if you want to write a different value into the database table for the parent table.

    Allan

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    OK, we are almost there...
    I fount this example "Logging changes":
    https://editor.datatables.net/manual/php/events#Examples

    <?php
    session_start();
    include( "DataTables.php" );
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    function IsClient( $db, $id ??? ) {
        $db->update( 'contacts', 
            array('cont_iscli' => 1 ),
            array('cont_id' => $id ) ); 
                             /****where contacts.cont_id = visits.visit_idcont***/ 
    }   
    
    Editor::inst( $db, 'visits.visit_id' )
        ->fields(
            Field::inst( 'contacts.cont_visits' ),
            Field::inst( 'visits.visit_user' )->set( Field::SET_CREATE ),  
            Field::inst( 'visits.visit_date' ),
            Field::inst( 'visits.visit_idcont' )
                ->options( Options::inst()
                    ->table( 'contacts' )
                                   ->value( 'cont_id' )
                                   ->label( 'cont_name' )
                      ->where( function ($q) {
                        $q->where( 'cont_user', $_SESSION['id'], '=' );
                    } )
                )
                ->validator( Validate::dbValues() ),               
            Field::inst( 'contacts.cont_name' ),           
            Field::inst( 'visits.visit_note' )
        )
        ->on( 'preCreate', function ( $editor, $values ) {
            $editor
                ->field( 'visits.visit_user' )
                ->setValue( $_SESSION['id'] );
        } )
    
        ->on( postCreate', function ( $editor, $values ) {
            IsClient( $editor->db(), $id ??? ); 
        } ) 
    
        ->leftJoin( contacts', cont_id', '=', 'visits.visit_idcont' )
        ->where( function ($q) {
            if ( ! isset($_POST['contacts']) || ! is_numeric($_POST['contacts']) ) {
                $q
                    ->where( 'cont_user', $_SESSION['id'], '=' );
            }else{
                   $q->where( contacts.cont_id', $_POST['contatti'] );
            }
        } )
        ->process( $_POST )
        ->json();
    ?>
    

    I could change insert () with update ().
    I have to make this query:
    UPDATE contacts SET cont_iscli=1 WHERE cont_id=visit_idcont"
    but I need help to understand which variables I need to pass to the function to get the field names for the correct query...

    Thanks for your help

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    ->on( postCreate', function ( $editor, $values ) {

    This function signature is incorrect. For postCreate it should be:

    ->on( postCreate', function ( $editor, $id, $values ) {
    

    The PHP events documentation details the parameters that are passed into each event handler.

    Allan

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    Changes made!
    I still need to understand one last thing ...
    How do I pass the id of the parent table row to the function?
    If I write by hand in the function the value of "contacts.cont_id" everything works.
    Here you can see...

    function IsClient( $db, $campo, $pippo ) {
    $db->update( 'contacts',
    array($campo => 1 ),
    array('cont_id' => $pippo ) ); //manually set value of pippo works!
    }
    I used array() because I did not understand how to write individual values :)

    Editor::inst( $db, 'visits.visit_id' )
    visits.visit_idcont is the field value to pass to $pippo (where)
    Field::inst( 'visits.visit_idcont')
    ->options( Options::inst()
    ->table( 'contacts' )
    ->value( 'cont_id' )
    ->label( 'cont_name' )
    ->where( function ($q) {
    $q->where( 'cont_user', $_SESSION['id'], '=' );
    } )
    )
    ->validator( Validate::dbValues() ),

    ->on( postCreate', function ( $editor, $id, $values ) {
    IsClient( $editor->db(), 'cont_iscli', $pippo );
    } )

    Thanks for your help and patience...

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    It will not be very elegant but now it works!

    function IsClient( $db, $campo, $pippo ) {
        $db->update( 'contacts',
        array($campo => 1 ),
        array('cont_id' => $pippo ) ); 
    }
    ******
    ->on( 'postEdit', function ( $editor, $id, $values ) {
       IsClient( $editor->db(), 'cont_iscli', $values['contratti']['contr_cli']);
    } )
    

    thanks for the support

  • GargiucnGargiucn Posts: 104Questions: 29Answers: 0

    There may be a problem when I delete the rows of the "visits" child table:
    if I delete all "visits" the "contacts" will have to return to have value 0 on field contacts.contr_cli (no visits for this contact).

    Now I have to relax for the problem solved before ... ;)

This discussion has been closed.