So I am in the process of attempting to create a basic user system and within this system I want users to be able to have multiple roles.
Say for example I have the roles as follows: Administrator, Events Organiser, Donator
What is the best way to assign these multiple roles to a user and then check if they have any of these roles for showing certain permissions.
If it was only one role per person then it wouldn't be a problem as I'd just assign say Administrator = 10, Organiser = 5 and Donator = 1 and then do an if statement to check if the MySQL data is equal to any of those three numbers.
I can't imagine there is a way to add a MySQL Field and fill it with say "Administrator,Donator" and therefore that user would have both of those roles?
Is it just a case of I would need to create 3 separate fields and put a 0 or a 1 in those fields and check each one separately?
Use multiple tables and join them:
User
--------------
id name
1 test
Role
--------------
id name
1 Donator
2 Organizer
3 Administrator
User_Role
--------------
id user_id role_id
1 1 1
2 1 3
SELECT * FROM User u
LEFT JOIN User_Role ur ON u.id = ur.user_id
LEFT JOIN Role r ON ur.role_id = r.id
WHERE r.name = "Administrator";
The query is easier if you know you only have 3 roles and they are easy to remember.
SELECT * FROM User u LEFT JOIN User_Role ur ON u.id = ur.user_id WHERE ur.role_id = 3;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With