How do I filter a table on user roles? I understand how to only show users their entries. My scenario is that I have users submitting data in a form, going to a MySQL table. I’m displaying the data back to the user and then want to show the data for an area to a manager. The managers email and user role is captured in the form, so I just need to know how to filter the table based on the role of the user viewing the table.
If I understand your scenario correctly, you can add the placeholder %CURRENT_USER_ID% as the predefined filtering value in column settings of "User ID" column, and the table will automatically be filtered per the ID of the currently logged in user.
I hope this helps, do let us know if there is anything else we can assist you with.
If the dependency is a user role, for example, we could make it work like this:
If I have this table:
This is a manual table which has manually entered IDs from wp_users and roles from wp_usermeta:
So, then I go to create an SQL query based table which will display entries based on user roles using this query:
SELECT new_table.id AS ID,
new_table.role AS Role,
new_table.product AS Product,
new_table.amount AS Amount
FROM wp_wpdatatable_6 AS new_table
JOIN wp_usermeta AS new_table_1
ON new_table_1.user_id = %CURRENT_USER_ID%
AND new_table_1.meta_value LIKE CONCAT('%', new_table.role, '%')
When I'm logged in with user ID = 1, I see this:
When I'm logged in with user ID = 4, I see the same thing. And when I'm logged in as one of the subscribers, I can only see the rows where role = subscriber:
The only difference is that as subscriber I don't have access to back-end, so this confirms it is working on both front and back.
hello,
How do I filter a table on user roles? I understand how to only show users their entries. My scenario is that I have users submitting data in a form, going to a MySQL table. I’m displaying the data back to the user and then want to show the data for an area to a manager. The managers email and user role is captured in the form, so I just need to know how to filter the table based on the role of the user viewing the table.
Hi Jeffrey
Thank you for reaching out to us.
If I understand your scenario correctly, you can add the placeholder %CURRENT_USER_ID% as the predefined filtering value in column settings of "User ID" column, and the table will automatically be filtered per the ID of the currently logged in user.
I hope this helps, do let us know if there is anything else we can assist you with.
I need to filter by the user role on a table, not just the id. How can I filter by user role?
If the dependency is a user role, for example, we could make it work like this:
If I have this table:
This is a manual table which has manually entered IDs from wp_users and roles from wp_usermeta:
So, then I go to create an SQL query based table which will display entries based on user roles using this query:
SELECT new_table.id AS ID, new_table.role AS Role, new_table.product AS Product, new_table.amount AS Amount FROM wp_wpdatatable_6 AS new_table JOIN wp_usermeta AS new_table_1 ON new_table_1.user_id = %CURRENT_USER_ID% AND new_table_1.meta_value LIKE CONCAT('%', new_table.role, '%')When I'm logged in with user ID = 1, I see this:
When I'm logged in with user ID = 4, I see the same thing. And when I'm logged in as one of the subscribers, I can only see the rows where role = subscriber:
The only difference is that as subscriber I don't have access to back-end, so this confirms it is working on both front and back.
Awesome! I will try this
Do let us know if you need any further assistance, we are always happy to help
Have a wonderful day!