0

I've got two tables in my database, one containing groups and one containing users. The users can be a member of those groups even multiple times.

So users contains "id", "name", "lastname", "group", and some others which are not important atm. The table groups contain just an id and a group name. Those groups are dynamic, sometimes a group gets added, and sometimes a group gets deleted.

On the front end, people can add themselves to a group using a form with a select multiple

But I do not get any further than this. I'm facing two issues. data collected from an are in an array, so putting them in a SQL query isn't plain simple. (i was thinking to put al the info from the array into just one variable separated by ",") on the other hand, maybe this table format isn't the best way to do so.

So what I'm I asking from you guys. Is it better to change the table structure and into what? How do I combine an array into one variable so I can put them in the database (separated by ",")<\s>

EDIT!: As recommended by Andy Hall, I 'v created a new table. werkgroep_user with id, werkgroep_id, and user_id.

this is what my select looks like

<select id="testwerkgroep" name="testwerkgroep[]" multiple>
 <option value="">geen werkgroep</option>
 <option value="1">werkgroep 1</option>
 <option value="2">werkgroep 2</option>
</select>

Now I'm facing the problem of getting data from the DB. I want for example all users(name) and their groups(name)

Mark
  • 55
  • 1
  • 10
  • Can you edit your post with what you've tried? – Mech Mar 13 '20 at 17:29
  • 1
    `this table format isn't the best way to do so` is correct. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad To answer `How do i combine an array into one variable so i can put them in the database (separated by ",")` though would be `implode`. – user3783243 Mar 13 '20 at 17:33

1 Answers1

2

Looks like your Users and Groups tables have a many-to-many relationship. "Many users can be part of many groups".

Without resolving the many-to-many relationship with an intermediary table, you can certainly use a string of comma separated values in the users.group field. You would achieve that by using the implode() function on your array of group IDs:

$commaSeparatedGroupIdString = implode($groupIdArray);

And when you retrieve from the database, you can explode them back out to an array:

$groupIdArray = explode($commaSeparatedGroupIdString, ",");

The second (and, I would say better) method is to resolve the many-to-many relationship with an intermediary table. This table could be named something like group_assignments. One user may have many group_assignments, and one group may have many group_assignments.

The structure of the group_assignment table would be:

id primary key

user_id foreign key to the users.id field

group_id foreign key to the groups.id field

Whenever a user wishes to assign themselves to a group, they would make a submission, and your application would create a new record in the group_assignments table with the ID of the user and ID of the group they submitted to assign themselves to. Retrieving group assignment information for a user would then require the use of a JOIN in your SQL to grab all the associated group_assignment records to your user. Removing an assignment is as simple as removing the appropriate record in the group_assignments table.

Andy Hall
  • 454
  • 1
  • 5
  • 14
  • Alright i will try the better option in a few moments. Getting data will require a _double_ JOIN then since there are three tables involved? Since the data is provided as an array i need to do a foreach loop on adding the separate records to the intermediary table if i onderstand correctly? – Mark Mar 13 '20 at 19:07
  • Yes a double join would be needed to get the `group` records associated to your user through the intermediary table. And yes, a foreach per record is an acceptable way to do it. – Andy Hall Mar 13 '20 at 22:01