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

5/12/2010 1:44 pm | #1 troyd

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

5/13/2010 11:06 am | #3 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

5/14/2010 12:09 pm | #5 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

5/14/2010 12:11 pm | #6 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.

5/14/2010 5:11 pm | #8 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.

5/14/2010 8:31 pm | #9 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

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

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

5/12/2010 1:44 pm | #1 troyd