Manage Relational Tables not working

Platform: Windows 7 64bit, XAMMP, Site PHP

Trying to build a MRT page for a sporting site. In this site I want to be able to manage my Teams and their players. There will be many Teams and Players can be a member of more than 1 team.

So I was wanting to build a page so that as a Player registers (insert player) then they would choose which teams they will be playing for in the season. When the season is complete, before the next one starts, that player would update their details which would include updating their new team list (update player details)

I have been getting nowhere for the last few days trying to work out this MRT behaviour.

So for the purposes of this document, use the following:

Primary Table: Players
pkPlayerID - PlayerName
1 - Bob
2 - John
3 - Sam

Secondary Table: Teams
pkTeamID - TeamName
1 - Team A
2 - Team B
3 - Team C

Link Table: TeamPlayers
pkTeamPlayer - fkPlayerID - fkTeamID
1 - 1 (Bob) - 1 (Team A)
2 - 1 (Bob) - 3 (Team C)
3 - 2 (John) - 1 (Team A)
4 - 2 (John) - 2 (Team B)
5 - 2 (John) - 3 (Team C)
6 - 3 (Sam) - 3 (Team C)

So I created the 2 DA Insert / Update / Results page for "Teams" and "Players"

Then I went to update the Player_Insert page to add the MRT code.

Followed all of the steps outlined in a previous post showing how to create an MRT by Erik (showthread.php?p=10544)

1- I added a recordset to list the available Teams (pkTeamID, TeamName)

2- Created a new 2x2 table in the insert form and placed the TeamName in one col, and a checkbox in the 2nd col.

3- I highlighted the checkbox and clicked on the dynamic bolt, and selected pkTeamID as the value and chose 1 as the Equal to value.

4- Applied the Repeat Region behavior to this table row = my Teams Recordset.

5- Made sure that the redirect on the Insert Page was removed (had to do this manually as the Insert Record Wizzard would not remove it)

6 - Applied the MRT behaviour selecting the table "TeamPlayers" and selecting WADA_Insert_PLayer Session Variable as the Primary Link (pkTeamID) and the checkbox as the secondary Link (fkTeamID)

Uploaded the content and what I found was that because I selected "1" as the Equal to Value for the Dynamic Checkbox then the Repeat Region table would always display a tick against "Team A" on page load.

I would then tick various teams for that new Player and click on the insert button.

I would then look at the SQL Data and nothing would be inserted into the linked table.

I can understand pretty much of how the MRT works, but I am a bit confused on the checkbox and how to "Bind it" to a autoincrement Primary Key field. So I think that this part maybe where i am getting into trouble.

I have also tried working this the other way ie Insert_Team and choose which players are in that team. Same result no data entered into link table.

I have also removed the pkTeamPlayerID field from the link table, just keeping the 2 foreign keys. Same result no data entered into link table.

I have been going around in circles for days over this.

Regards

Dreamweaver does not give a simple way for binding the checked value for a check botx to dynamic data, you sort of have to do it manually.

In Design view, select the checkbox, then in the property inspector, hard code the initial value to 1. This will add value="1" to the checkbox code. with the checkbox still selected, go to split view, select the 1 in the value, then go to the bindings panel and select the ID column and click the insert button. This will replace the 1 with the dynamic data binding for the ID column.


If you are still having problems, please send a copy of your page so I can examine the code.

Jason

Ok, I followed your procedure to bind the TeamID into the checkbox. It now sort of works.

In my Team Table I have three teams A, B, C (TeamID 1,2,3)

When I open the insert page I will see all three teams listed (with no ticks) - Good

If I check only the last team (teamID = 3) then a row will be put into my table with the newly created playerID and teamid = 3

If I check all three boxes, and look at the link table only the last row has been inserted (in this case teamID=3)

If I check only the first two rows (Teamid 1 & 2) I get no entries in the link table.

So it seems that this code is only inserting the last value from the lookup table (Teams) into the LinkTable.

I repeated your suggestion with my Update Page and after manually adding into the linked table
playerID - TeamID
5 - 1
5 - 2
5 - 3

I opened the update page and would see all three teams displayed and checked - Good.
If I unchecked Team A (teamID =1) and click Update I noticed that the link table deleted both rows for TeamID 1 & 2 leaving on TeamID 3.

So by my testing even the Update Page will only manage the last record in the Teams table

I have attached the Insert / Update / Results pages as requested.

Further to this when I perform a View Source of the Insert Page I see the following for the repeat section:

<table width="359" border="0">
<tr align="center" >
<th scope="col">Add Player into Team</th>
</tr>
<tr>
<td align="left"><label>
<input type="hidden" name="WADA_RepeatID_pkRepTeamID_0" id="WADA_RepeatID_pkRepTeamID_0" value="1" />
<input name="cbInTeam_0" type="checkbox" id="cbInTeam_0" value="1" />
Team A</label></td>
</tr>
<tr>
<td align="left"><label>
<input type="hidden" name="WADA_RepeatID_pkRepTeamID_0" id="WADA_RepeatID_pkRepTeamID_0" value="2" />
<input name="cbInTeam_0" type="checkbox" id="cbInTeam_0" value="2" />
Team B</label></td>
</tr>
<tr>
<td align="left"><label>
<input type="hidden" name="WADA_RepeatID_pkRepTeamID_0" id="WADA_RepeatID_pkRepTeamID_0" value="3" />
<input name="cbInTeam_0" type="checkbox" id="cbInTeam_0" value="3" />
Team C</label></td>
</tr>
</table>

Should the "WADA_RepeatID_pkRepTeamID_0" / "cbInTeam_0" be incrementing ie. _0 _1 _2 etc as I noticed that this is a Counter. The fact that for all three rows in this table i am getting _0 suggests to me that once this is updated only the last row will be inserted / updated

try changing:

<?php } while ($row_Teams = mysql_fetch_assoc($Teams)); ?>

to:

<?php $RepeatSelectionCounter_1++; ?>

<?php } while ($row_Teams = mysql_fetch_assoc($Teams)); ?>

on the insert page.

Jason

I did what you suggested and added that additional line of code into both the Insert and Update Page, and guess what it worked.

While I have been waiting for this response, I did another test of this where I added an additional field into the link table called 'ShirtNo'

What I eventually want to do is when a Player registers and selects the teams that they will be playing in, they will enter what their shirt number is for that team.

E.g. Bob in Team A will have Shirt No 33 and in Team B will have Shirt No 27

So My link table should show something like

pkRepPlayerID - fkPlayerID - fkTeamID - ShirtNo
1 - 1 - 15 - 33
2 - 1 - 16 - 27

When I went through the steps I added a text box for ShirtNo into the RepeatRegion table and when I went through the MRT wizzard, in the last step I allocated the value of the text box into the ShirtNo table field.

When I tested this (which was prior to your response) I would get the entry into the link table for the last team id ok but I would get a null value for the ShirtNo field.

I then reran the MRT wizzard, but noticed that after the MRT has been applied I noticed that the binding for the ShirtNo field was now blank and I could not seem to find a value from the Lightning bolt to bind to it.

If from your last posting telling me to add a extra line of code into the Insert / Update Page (which has fixed the issue of selecting multiple items for Insert & Update) it seems to me that there seems to be some issues with the way the MRT process writes it's code. Coupled with the fact that I cant seem to be able to also add additional data into the linked table (which from reading previous posts in this forum) you should be able to do so.

I am running the latest version of DataAssist (2.0.6) which was installed into Dreamweaver CS4 on a clean build on WIndows 7 (ie no previous version was installed prior) so that rules out any extension upgrade hangovers

So is this a bug. Will it be fixed in later dot releases of DA?

When you have spent a lot of money (I purchased the SuperSuite) and find that in order to run a standard extension which doesnt seem to work properly (ie not inserting code) that it should, and you have to get lines of code from your company (which I do appreciate the support process) to make these things do what they should. Means that to use a set of tools that should make the development cycle reasonable quick especially for people just starting in this game and then we have to remember to add a line of code that doesnt get inserted into future implementations, seems to defeat the purpose of using these tools in the first place.

To be fair, I have not tried to this run this MRT before so I cannot say for certan if this incident is because this line of code isnt part of the wizzard creation process or wether it is a weird Dreamweaver having glyches running extensions thing. (ie On my previous platform, that was 32bit Vista 4 GB Ram I had a huge number of failings trying to run through the Site Assist Pro wizzard for example, where screens would lock up all the time, now that I am on this new platform with 8 GB ram 64bit I seemed to now have less of these issues, although not fully tested running Site Assist yet.)

But now that I have got the insert / update part working, I will now recompile a fresh page and start this MRT process again, this time trying to get the additional fields (shirtNo) to add. If I am unable to do so Ill attach the new .php pages for you to look at.

Regards

Yes, this is a bug with the way the repeat region behavior writes the code.

there are two behaviors in play, the repeat region and the repeat selection. The issue is that repeat selection behavior should be applied inside the repeat region behvior, in you case it is applied outside the repeat region behavior which causes the problem.


One way that I have found to work around the problem is to not include the repeated form elements inside the table.

when the repeated form elements are displayed in a table, it causes the repeat region code to be generated incorrectly.


This _is an issue that has been logged and will be fixed in a future update to DataAssist