[SOLVED] many-to-many relationships

[vandana]

Hi,

 

I have a table with groupnames, a table with groupmembers and a third table which contains the relationship between the group and groupmembers.

 

Group table:

group_id group_name

1           Testers

2           Developers

 

Groupmember table:

Groupmember_id   groupmember_name

1                        Jaap

2                        Tim

3                        Mary

4                        Klaas

 

Group_groupmembers table

group_id   groupmember_id

1                1

1                2

2                3

2                4

So in Group 'Testers' I have Jaap and Tim and in Group 'Developers' I have Mary and Klaas.

 

When I edit group 1 (Testers) and click Jaap and Mary.

Jaap is updated, Mary is inserted but how to delete Tim from the group.

 

I get the selected members as

Array

(

    [0] => 1

    [1] => 4

)

 

So 1 (Jaap) is updated, 4(mary) is inserted but how to delete Time. How can I make my delete statement as clever as possible??

 

What is the best way to handle these delete in many-to many relationships.

 

The insert works fine, update works fine, but now the delete.

 

Vandana

[Nhoj]

I know this isn't exactly what you're looking for, but, it might be much more feesible to simply have your groupmember table include said users group ID. For example:

 

Groupmember table:

Groupmember_id  group_id              groupmember_name

1                        1                                  Jaap

2                        1                                  Tim

3                        2                                  Mary

4                        2                                  Klaas

 

This would allow you to completely remove an entire table and also simplifiy the updating, inserting and deleting.

 

If you absolutely require that 3rd table, I do apologize and simply ignore my suggestion

[vandana]

Hi,

 

Thank you for your answer, but this will not work in my case. Because a groupmember can be linked to different groups.

So jaap can be in group 1, but also in group 2 or 3.

That's why I made the third table to manage these many-to-many relationships.

 

Vandana

[JasonLewis]

You could allow an array into the group_id field.

 

Like:

Groupmember_id   group_id              groupmember_name

1                        1,2                              Jaap

2                        1                                 Tim

3                        1,2                              Mary

4                        2                                 Klaas

 

That way, Jaap and Mary are both in group 1 and 2. Wouldn't be to hard to modify your code a little bit to allow for this.

[Nhoj]

Another way to probably set this up to delete would be to either some how create an array of deselected users and do something like:

 

 

foreach ($notselected as $userid) {

     DELETE FROM `Group_groupmembers` WHERE `group_id` = $group AND `groupmember_id` = $userid

}

 

Or what you could do is do the same thing essentially except start out by deleting EVERY user from the group with somethin glike

 

DELETE FROM `Group_groupmembers` WHERE `group_id` = $group

 

Then re-insert anyone you added like this, assuming the array of selected users is $selected:

 

foreach ($selected as $userid) {

     INSERT INTO `Group_groupmembers` VALUES ($group, $userid)

}

 

EDIT: Essentially with the 2nd way you are removing EVERYONE from the group and then re-adding only a few... It would probably be the simplest solution imho

[vandana]

Hi NHoj,

 

Thank you for your suggestion. It works now and my code is much easier. I only have to delete all entries, than use insert and not use update anymore.

 

Vandana