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

Gargiucn

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...

allan

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

Gargiucn

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 ...

allan

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

Gargiucn

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();

<?php > ``` Thank you again for your patience ... ?>

allan

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

Gargiucn

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...

allan

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

Gargiucn

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();

<?php > ``` 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

allan

->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

Gargiucn

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...

Gargiucn

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

Gargiucn

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 ...