close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

How can I get Multiple Insert, Update, Delete with MRT all working together??

Thread began 5/12/2010 4:44 pm by troyd | Last modified 5/17/2010 8:08 pm by Eric Mittman | 1224 views | 9 replies |

troyd

How can I get Multiple Insert, Update, Delete with MRT all working together??

I have a 3 table db that includes a Category table, Sub Category table and a relational table (see 1st image).

I then have an insert, update, delete multiple records for both the Category and Sub Category table. This works great. (2nd image).

The insert/update/delete fields are in a repeat selection, similar to an example I saw for the BlueSky admin. This works just fine.

But I needed to take it further.

When inserting the Sub Category, I need to also insert records into the relational table so that each Category is related to the Sub Category. I have this working by using the Manage Relational Table behavior and including a multiple select in the Sub Category form that is dynamically populated by the Category table. (But I had to use a single record insert within this part).

Here's my problem. I can't figure out how to delete the records from the relational table at the same time the records are being deleted from the Sub Category table.

Do I add a second MRT behavior? or a second Delete Behavior?

And one last thing. In the MRT the key value is the "key value set to the session variable declared in the Single Record Insert server behavior". But what do we use in the MRT for a multiple record insert behavior?

Thanks,
TroyD

Sign in to reply to this post

Jaffa

Add a second Delete server behaviour within your form server behaviours using the same trigger as the first delete.

Delete from relationship table where [ID] = SubCatID

Sign in to reply to this post

troyd

Originally Said By: Jaffa
  Add a second Delete server behaviour within your form server behaviours using the same trigger as the first delete.

Delete from relationship table where [ID] = SubCatID  



Thanks so much Jaffa,

I have tried doing this, but to be honest, I'm not grasping it within the code. The second multiple delete behavior is pasted below. I assume that I need to hand code the WHERE clause towards the bottom. But everything I try, fails.

A side note, my repeat selection uses a recordset that references only the Sub Categories table. Is there anything I need to add to the repeat selection to loop through the relational table? Or should I be using a JOIN recordset including both the SubCategory and relational table together for this repeat region/selection?

php:
<?php

// WA DataAssist Multiple Deletes
if (isset($_POST["UpdateSubCatBtn_x"])) // Trigger
{
  if (!
session_id()) session_start();
  
$WA_loopedIDField = array("WADA_RepeatID_TableID");
  
$WA_loopedField = array("DelSubCatchecked");
  
$WA_connection $dbStore;
  
$WA_table "relCat_SubCat";
  
$WA_redirectURL "products_Categories.php";
  
$WA_keepQueryString false;
  
$WA_indexField "TableID";
  
$WA_columnTypesStr "none,none,NULL";
  
$WA_fieldNames explode("|"$WA_indexField);
  
$WA_columns explode("|"$WA_columnTypesStr);
  
$WA_comparisions = array("=");
  
$WA_connectionDB $database_dbStore;
  
$WA_multipleDeleteCounter 0;
  
mysql_select_db($WA_connectionDB$WA_connection);
  while (
WA_AB_checkLoopedFieldsNotBlank($WA_loopedIDField$WA_multipleDeleteCounter)) {
    if (
WA_AB_getLoopedFieldValue($WA_loopedIDField[0], $WA_multipleDeleteCounter) == WA_AB_getLoopedFieldValue($WA_loopedField[0], $WA_multipleDeleteCounter)) {
      
$WA_fieldValuesStr WA_AB_getLoopedFieldValue($WA_loopedIDField[0], $WA_multipleDeleteCounter);
      
$WA_fieldValues = array($WA_fieldValuesStr);
      
$deleteParamsObj WA_AB_generateWhereClause($WA_fieldNames$WA_columns$WA_fieldValues$WA_comparisions);
      
$WA_Sql "DELETE FROM `" $WA_table "` WHERE " $deleteParamsObj->sqlWhereClause;
      
$MM_editCmd mysql_query($WA_Sql$WA_connection) or die(mysql_error());
    }
    
$WA_multipleDeleteCounter++;
  }
  if (
$WA_redirectURL != "")  {
    if (
$WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
      
$WA_redirectURL .= ((strpos($WA_redirectURL'?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
    }
    
header("Location: ".$WA_redirectURL);
  }
}
?>



Lastly, on this delete behavior, do I use the TableID or the Parent_SubCategoryID for the index field? $WA_indexField = "TableID";
In this table, as you can see, the TableID is the unique column.

Thanks again,
TroyD

Sign in to reply to this post

troyd

I'm starting to feel like I am on some sort of an "ignore list" at WA. :)
Did everyone go on vacation?

TroyD

Sign in to reply to this post

troyd

Ok, I think I have it figured out and I wanted to share just in case someone else tries this. Keep in mind, I just fixed this, so I might encounter other problems but so far it's working.

The recordset that populates the Sub Category repeat selection (see second image above) had to be recreated as a JOIN so that I brought the dependencies in from the relational table. But my first attempt caused a problem. My first JOIN looked like this;

SELECT SubCategoryID, SubCategoryName, Parent_SubCategoryID
FROM tblSubCategories LEFT JOIN relCat_SubCat ON Parent_SubCategoryID = SubCategoryID


The above was wrong because my query showed multiples of each Sub Category name.

Food
Food
Food
Bowls
Bowls
Bowls
Toys
Toys
Toys

Since there were 3 records at the time, for each Sub Category in the relational table, it was showing me everything.

After some study, I figured out I needed to include a GROUP BY in my statement. So I changed it to this;

SELECT SubCategoryID, SubCategoryName, Parent_SubCategoryID
FROM tblSubCategories LEFT JOIN relCat_SubCat ON Parent_SubCategoryID = SubCategoryID
GROUP BY SubCategoryID


So now I get this;
Food
Bowls
Toys

(This was very exciting to discover. The more I learn about JOINS the more control I find I have over everything).

Then, on to the delete for the relational table. I did just as Jaffa suggested and added a second delete behavior. It's a multiple delete records behavior. I used the same delete checkbox as the key, just as the first uses. But I referenced the relational table and Parent_SubCatID column instead of the unique TableID column. So even though it's getting the SubCatID from the first table, it uses that same value anyway to then look into the Parent_SubCatID column on the relational table and delete how ever many rows use this value.

One thing I did have to do by hand is change some values in the second multiple delete behavior because it tries to name things to make the second tables column. For example, this line

php:
$WA_loopedIDField = array("WADA_RepeatID_Parent_SubCategoryID");



had to be changed to

php:
$WA_loopedIDField = array("WADA_RepeatID_SubCategoryID");


to match the hidden field used in the form, for the first delete. I guess I could have added a second one but I don't know if that would have worked. This did.

So anyway, using the MRT, I can now insert new Sub Categories with Categories assigned to them within the relational table, then with a second delete behavior, it's deleting from both tables just fine. I'll report back if there are any problems. And if anyone sees a better way for me to do this, please let me know.

Thanks,
TroyD

Sign in to reply to this post

Eric Mittman

I saw this earlier but did not want to jump into the middle of your conversation. I agree with what jaffa is saying here, you can add in another delete server behavior. A single delete server behavior can work like a multiple delete since you can base it on a key value. If you use the category id when deleting from the sub categories table you will delete all entries where the id matches, you can do the same thing with the relational table, just delete where the id matches the one you are deleting.

Sign in to reply to this post

troyd

Eric,

I think we posted about the same time. Can you look at my post right before yours and let me know what you think?

Thanks,
TroyD

Sign in to reply to this post

Eric Mittman

I did not notice the post before I made mine, it looks very good to me. I would suggest doing some extensive scenario testing with it though to ensure that it is actually deleting the related records as it should be. So long as you are getting the desired result when testing it out I think it is a good way to go. This seems to be a slightly more advanced than normal scenario but I'm sure that anyone looking to do similar will gain a tremendous amount of info from your last post. Thanks for getting back with the details, it always helps.

Sign in to reply to this post

troyd

Thanks Eric,

Yes, I am testing it and so far it works. I basically have a second window open, looking at the tables in phpMyadmin while I insert new Sub Categories. I do see 1 new single record in the main Sub Categories table, as it should be. And multiple related records in the relational table displaying the matching foreign key ID. Then, when I delete the Sub Category, it removes all records in both tables.
After hundreds of tests, not seeing the records removed from the relational table, I can't tell you how excited I was when I finally watched them disappear. I won't move this to the live site until I am sure, but so far so good.

TroyD

Sign in to reply to this post

Eric Mittman

That is great to hear, let us know if you have any further questions.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...