0

I have an idea of doing a back office for my website. The problem is that, when I introduce a user who has, for example, two roles, I want the user to choose the role to work with, but I can't figure out how to do that. I've tried so different things such as putting the two roles separate with commas in MySQL and separating them in PHP but it didn't work.

Henrique Araujo
  • 1
  • 3
  • Welcome to Stackoverflow, if you share specific code we may be able to help, maybe there is a syntax problem that you haven't seen. You should try to be more specific for example when I try A I get B but I wanted C . Maybe you take a look at this question first https://stackoverflow.com/help/how-to-ask – J.J Nov 09 '21 at 15:15

1 Answers1

-1

let say you have a user table, and a user can have multiple role.

you can create a master table for role ( let say roles )

role table

you can have a field on user table say user_roles -> it can have values like 1,2,4

note:- there should not be gaps between the numbers / comma

on the front-end you can take a mutiselect / tags UI element to input role for the user.

to fetch the role of a user at server side - you can use explode() + in_array() to check the role if exists

comma separated example

EDIT:-

It requires validation when adding roles ( ie: which user's role is allowed to select when already selected specific types ) For authorization, the user's roles should be added to the session :- this will help when allowing the user to do tasks he is allowed to do.

you can take a third table to maintain user's roles ( as suggested by @ADyson ), this will help if you want to join tables based on user + roles but you will need to join roles table for getting user data when starting session for the user.

D Coder
  • 331
  • 2
  • 10
  • In this case, I'm doing with "admin", "trainer" and "athlete". How can I adapt this to your solution? – Henrique Araujo Nov 05 '21 at 15:17
  • A better design would create a 3rd UserRole table, to implement a normalised many-to-many relationship – ADyson Nov 05 '21 at 16:18
  • @ADyson yes no doubt that also can be a good solution as well, but any how it needs validation while assigning the user's role, here the user has multiple roles. adapting comma separation will check only one table ( user's table ) which will be easy I think. But i don't understand why downgrade this as a possible approach. – D Coder Nov 07 '21 at 05:48
  • Validation will be much easier with a better table structure. You should also read https://stackoverflow.com/a/3653574/5947043 to see why comma-separated lists in a database can cause problems. – ADyson Nov 07 '21 at 09:02
  • Thanks @ADyson, i checked your link and its true, i also do not prefer to store values with delimiter, but in this case it was simple, and for "user_role" - it is mostly stored in the session, and i never needed to make it as a foreign key. about duplicate issue:- this can be checked while adding, and user_role is not frequently updated. Also - many framework has in-built method to convert it to array and vice-versa. And user_role is very limited can be max around 5 to 10. I wd prefer comma here because it will need only one table while login and then onward it will be in the session. – D Coder Nov 07 '21 at 10:58
  • Also, in this case it will store homogeneous value ( integer only ) . About finding user with user type (s), there are many methods in MySql. There are array, JSON type data supported by MySql/PHP. If adding tables is the only solution, there would not be such option. It depends on the need and scale. – D Coder Nov 07 '21 at 11:06