I am making a menu, but I want to limit it to only some users with a specific permission can see it.
the query:
SELECT idpermission,userid FROM user_permissions WHERE userid = “U001”
Result: User U001 has 3 permissions stored.
idpermission,userid IDP001,U001 IDP002,U001 IDP003,U001
I have 3 tables (users,permissions and user_permissions), in user_permissions I store each permission of each user
Example: Table user | permission | user_permissions result: user: u001,carlos,carlos@... permissions: IDP001 = book | IDP002 = create_book | IDP003 = edit_book | IDP004 = user | IDP005 = edit_user... user_permissions: IDP001,U001 IDP002,U001 IDP003,U001 IDP001,U002 IDP003,U002...
when i call the query
while($sqlRow=$sqlPermissions->fetch(PDO::FETCH_ASSOC)) { if ($sqlRow['idpermission'] == 'IDP001' || $sqlRow['idpermission'] == 'IDP002' || $sqlRow['idpermission'] == 'IDP003') { echo "user menu "; } }
but the result that appears to me is 3:
user menu user menu user menu
You should only show me one, making only a comparison if you have such permission shows but shows nothing.
What I want to reach is that the user can have many permissions for different items on the menu, such as view, edit, create, delete, each one is a permission, and each item (user menu, books menu) is another permission.
Advertisement
Answer
Use a pivot technique to gather all permissions for a single user and form a single row to fetch with pdo.
This way all expected columns are declared and you can use simple truthy/falsey conditional checks in your php for any of the permission settings.
So long as the userid exists in the table, you will have a fully populated row to access. This will be clean, direct, efficient, and easy to maintain.
Schema (MySQL v5.7)
CREATE TABLE user_permissions ( idpermission VARCHAR(20), userid VARCHAR(20) ); INSERT INTO user_permissions VALUES ('IDP001', 'U001'), ('IDP002', 'U001'), ('IDP003', 'U001'), ('IDP001', 'U002'), ('IDP003', 'U002');
Query #1
SELECT MAX(IF(idpermission = 'IDP001', 1, 0)) AS book, MAX(IF(idpermission = 'IDP002', 1, 0)) AS create_book, MAX(IF(idpermission = 'IDP003', 1, 0)) AS edit_book, MAX(IF(idpermission = 'IDP004', 1, 0)) AS user, MAX(IF(idpermission = 'IDP005', 1, 0)) AS edit_user FROM user_permissions WHERE userid = 'U001' GROUP BY userid;
Result set:
| book | create_book | edit_book | user | edit_user | | ---- | ----------- | --------- | ---- | --------- | | 1 | 1 | 1 | 0 | 0 |