Only display records relevant to user

I have five different user groups (numbered 1-5) in dB column called MembershipType . Two of these user groups are Administrators (MembershipType= 1 & 5). Type 1 can view and update all user records, but Type 5 should only be able to view & update those with MembershipType=3&5.

The WADAusers recordset on the results page (set up using the DataAssist wizard) is currently:

SELECT UserID, UserEmail, UserPassword, UserFirstName, UserLastName, CompanyName, MembershipType, UserRegistrationDate
FROM users
WHERE users.userVerified = 1
ORDER BY UserLastName ASC

This displays all the user records.

I already have a session variable for MembershipType set up. How would I amend the above Recordset so that Administrator user group 1 can view as normal, but Administrator user group 5 only sees MembershipType 3 & 5?

edit the where clause to:
WHERE users.userVerified = 1 AND (1 = paramMembershipType) OR ((paramMembershipType2 = 3 AND MembershipType = 3) OR (paramMembershipType3 = 5 AND MembershipType = 5))

then create 3 parameters as:

Name: paramMembershipType
Type: Integer
Default Value: -1
Runtime value: $_SESSION['MembershipType']

Name: paramMembershipType2
Type: Integer
Default Value: -1
Runtime value: $_SESSION['MembershipType']

Name: paramMembershipType3
Type: Integer
Default Value: -1
Runtime value: $_SESSION['MembershipType']

Hi Jason

Thank you so much for that, great service as always! I had to make one slight change in the WHERE clause, last bit changed to

OR (paramMembershipType3 = 5 AND MembershipType = 3))

so that the Admin type 5 can view user group 3. All working!

glad to hear you have it working.

Spoke too soon! Because this system relies on admin approval of users, I have a separate page which shows all users awaiting approval. I copied over the above code to the recordset on that page, just changing users.userVerified = 0, however I now find that when I'm logged in as Admin user group 5, it's not working properly. It's fine for Admin user group 1, but Admin 5 sees verified and unverified members on the main page, and nothing on the approval page. Probably best if I give you the actual page details - see PM section.

i updated the where cluase on those 2 pages to:


WHERE users.userVerified = 0 AND ((1 = paramMembershipType) OR (((paramMembershipType2 = 3 OR paramMembershipType3 = 5) AND (MembershipType = 3 OR MembershipType = 5))))

Once again, thank you - works like a dream!

you're welcome.