Subform: delete records from referenced table

Subform: delete records from referenced table

Postby Tinka » Fri Jul 10, 2015 8:52 pm

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.com/viewtopic.php?f=4&t=5977&p=7153&hilit=loop#p7153

Please advice.

BR, Tinka
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: Subform: delete records from referenced table

Postby admin » Mon Jul 13, 2015 3:42 pm

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
admin
Site Admin
 
Posts: 3223
Joined: Mon Jun 15, 2009 9:53 am

Re: Subform: delete records from referenced table

Postby Tinka » Tue Jan 24, 2017 7:18 pm

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
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: Subform: delete records from referenced table

Postby admin » Wed Jan 25, 2017 12:23 pm

Tinka,

Try this.. http://wiki.nubuilder.net/nubuilderv3/index.php/PHP_Functions#nuGetJSONSubform.28.24formName.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
admin
Site Admin
 
Posts: 3223
Joined: Mon Jun 15, 2009 9:53 am

Re: Subform: delete records from referenced table

Postby EcoReality » Thu Feb 16, 2017 10:34 am

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. :-(
Last edited by EcoReality on Mon Mar 20, 2017 2:22 am, edited 1 time in total.
:::: Jan Steinman EcoReality Co-op ::::
EcoReality
 
Posts: 26
Joined: Wed Feb 15, 2017 5:20 pm
Location: Salt Spring Island, British Columbia, Canada

Re: Subform: delete records from referenced table

Postby admin » Sat Feb 25, 2017 8:55 am

Jan,

Feel free to make a fork.

Steven
admin
Site Admin
 
Posts: 3223
Joined: Mon Jun 15, 2009 9:53 am


Return to General