Looking for some advice for storing multiple users in a column

Wappler General How To
#1

Hi,

I’m looking for some advice on how to handle this best, I’ve tried a few ways, but I’m really not convinced they will work or are the best way to handle this.

Essentially, I have a MySQL table with multiple rows of categories, for each row, there is an “Assigned” column. I want to store the security identities of multiple people in this column, so they can then be retrieved for use later to check if they are assigned to that category.

What is the best way of me going about this? The row needs to work in such a way where I can retrieve each user from it individually, and remove/add users individually as needed (while keeping the other users in it). You can also add a category from inside the web app.

I was originally thinking of an array, but this doesn’t seem all that suitable for this instance, seeing as categories can be dynamically added.

#2

The best would be to store the users in separate rows, not in a single row.
So you have a database table storing your categories and another one storing users and the category ID each user is assigned to.

Simple example:

category_id | category_name
1           | category 1
...
99          | category 99

user_id | category_id | user_name
1       | 1           | John Doe
2       | 1           | Jane Doe
3       | 2           | Donald Trump
...
1 Like
#3

Yeah so this was my original idea, was really just wondering if there was a better way to do it, especially when there because a large number of categories and users, which there will be.

I guess with this method, my only question is that on the user edit page, where you can assign users to a category, I have a multiple select option that feeds data from the database. How can I insert all which the user selects as separate entries into the table above (as an example)?

The value field is already set for the multiple select, but do I need to create a repeat insert in the server action? I guess just a normal “Database Insert” doesn’t suffice here since it will only add in one column.

#4

Yes. Send all the data to the server action (an array is the most logical way) and then create a repeat to go through that array inserting each time.

#5

Alrighty then, thanks, I’ll give this a try later tonight.

1 Like
#7

Thanks for your help so far. The steps above have allowed me to get everything working correctly.

I was just wondering now the best way to display only category names that the person has access to.

In essence:

  1. Checks the usergroup table, to see what category ID’s a user has access to.
  2. Checks the category table, only fetching those that match the IDs the user has access to in the usergroup table.