Subform: delete records from referenced table

[Tinka]

Hi!

I am struggling with using JavaScript and php to delete records from a table which is referenced by the subform when deleting a record from the Edit form.

I have three tables A, B and the many-to-many link A-B table C.
My form is based on A, the subform on table C.

I have this php code on form A After Delete, which actually does delete the respective records in table C:

Code: Select all

$sqla = "
DELETE FROM transcr_geneMMlink
WHERE gene_idFK = '#RECORD_ID#'

";

nuRunQuery($sqla);

So this part is working.

Now, I want to delete the respective records in table B too, by PHP code on the After Delete tab of the form.
My object on the subform is a lookup, this field value I can use to find the records in table B.

I wrote a function to copy the value of the lookup field to a new object on the SF by form A Javascript, for using this field value in the After Delete PHP:

Code: Select all

function AddTrans(){
    var s = nuSubformArray('SFMtranscripts', false);
    
    for(i = 0 ; i < s.length ; i++){                                            //-- loop through subform rows
        var trans = $('#' + s[i] + 'transcr_idFK').val();
        
        //alert(trans);
        $('#' + s[i] + 'transid').val(trans);
        
        }
}

I call that function under nuLoadEdit. Works.


So how can I loop through the subform rows in php and obtain the value of that field? I am only able to obtain the primary key of the subform row:

Code: Select all

//delete records from transscripts table and many-to-many link table genes-transscripts

$subFormPrefixes = nuSubformArray('SFMtranscripts', true); //--fetch all of the prefixes for the subform, also those ticked for deletion


//loop through all rows
for( $idx = 0; $idx < count($subFormPrefixes); $idx++) {

    $search = nuF($subFormPrefixes[id] + 'transid'); //field updated by form Java
    
    $sql = "
    DELETE FROM transcript
    WHERE transcript_id = '$search'
    ";
    
    nuDebug($sql);
    nuRunQuery($sql);

}

How can I pass a variable/field value from Javascript to the PHP?
I can see there is a PHP function nuGetJSON subform - but I don't know how to use it.
Or do I have to use $_Post somehow like in this thread:
http://forums.nubuilder.cloud/viewtopic.p ... loop#p7153

Please advice.

BR, Tinka

[admin]

Tinka,

Run as a Procedure ..

Code: Select all

$j = '[ 
{ "btn_zite_zzz_product_service_id" : "",
    "codezite_zzz_product_service_id" : "RL30",
    "descriptionzite_zzz_product_service_id" : "30 cm Ruler",
    "nuDelete" : false,
    "nuIndex" : 0,
    "nuPrimaryKey" : "54c8551569c0f91",
    "zite_cost" : "1.00",
    "zite_description" : "30 cm Ruler",
    "zite_tax_percent" : "10.00",
    "zite_units" : "2.00",
    "zite_zzz_product_service_id" : "54c85436d3b9ea5"
  },
  { "btn_zite_zzz_product_service_id" : "",
    "codezite_zzz_product_service_id" : "RL100",
    "descriptionzite_zzz_product_service_id" : "1m Ruler",
    "nuDelete" : false,
    "nuIndex" : 1,
    "nuPrimaryKey" : "54c855157221c8b",
    "zite_cost" : "3.00",
    "zite_description" : "1m Ruler",
    "zite_tax_percent" : "10.00",
    "zite_units" : "2.00",
    "zite_zzz_product_service_id" : "54c8546ac5cbc99"
  },
  { "btn_zite_zzz_product_service_id" : "",
    "codezite_zzz_product_service_id" : "",
    "descriptionzite_zzz_product_service_id" : "",
    "nuDelete" : true,
    "nuIndex" : 2,
    "nuPrimaryKey" : "",
    "zite_cost" : "",
    "zite_description" : "",
    "zite_tax_percent" : "",
    "zite_units" : "",
    "zite_zzz_product_service_id" : ""
  }
]';

$object = json_decode($j);

print count ($object).'<br>'.$object[1]->zite_description;

will give you..

3
1m Ruler


Steven

[Tinka]

Hi Steven

I had it almost right.

Changed

Code: Select all

$search = nuF($subFormPrefixes[id] + 'transid'); //field updated by form Java

To

Code: Select all

$search = $nuHash [$subFormPrefixes[$idx].'transid'];

and now the related records in table C are deleted.

This does not work with a lookup or display field in the subform but with the hidden field with the PK of the children (not the PK in the subform) which is updated by Java.

BR, Tinka

[admin]

Tinka,

Try this.. http://wiki.nubuilder.net/nubuilderv3/i ... ormName.29

This will give you a string.

This string can be run through a PHP function to turn it into an object you can loop though.

http://php.net/manual/en/function.json-decode.php

Steven

[EcoReality]

Perhaps you can let MySQL do the work?

If both tables are InnoDB tables, and you declare a relationship between them. Set that relationship to CASCADE so that if the parent record goes away, the child record will go away, as well.

Here is how it works.

Ah, but that would probably confuse nuBuilder, unless you let it know, as well.

The advantage of letting MySQL do it is that it is transaction-safe. Otherwise, there is the possibility that someone could get in there and create a dependency on the parent record before your code can make it go away.

UPDATE: I discovered that nuBuilder does not work with InnoDB foreign key constraints, so please forget that idea, lest you spend many hours figuring out why things suddenly broke.

[admin]

Jan,

Feel free to make a fork.

Steven