Is there a quick way to find the number of members who have edit right on a given table. This is a once off so just some SQL should do the trick.
Many thanks
Barry
Edit rights
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
Hi Barry,
sure, have a look and try the following SQL command as a starting point:
Executed on your database it should give you a result like this:
If you only need one table, replace the line...
by ...
or by...
Hope this helps!
Best Regards,
Jan
sure, have a look and try the following SQL command as a starting point:
Code: Select all
select
membership_grouppermissions.tablename as 'tablename',
count(*) as 'num_editors',
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
group by tablename
order by 1
If you only need one table, replace the line...
Code: Select all
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
Code: Select all
AND membership_grouppermissions.tablename IN ('YOURTABLE_1')
Code: Select all
AND membership_grouppermissions.tablename = 'YOURTABLE_1'
Best Regards,
Jan
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
Thank you Jan,
worked perfect once I put the extra ' in YOUTABLE_2' ('YOURTABLE_1', YOURTABLE_2') > ('YOURTABLE_1', 'YOURTABLE_2').
great quick response
Barry
worked perfect once I put the extra ' in YOUTABLE_2' ('YOURTABLE_1', YOURTABLE_2') > ('YOURTABLE_1', 'YOURTABLE_2').
great quick response
Barry
Re: Edit rights
My typo, sorry!
Cannot edit the original post, so here is the updated SQL code:
Replace YOURTABLE_1 and YOURTABLE_2.
Regards,
Jan
Cannot edit the original post, so here is the updated SQL code:
Code: Select all
select
membership_grouppermissions.tablename as 'tablename',
count(*) as 'num_editors',
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', 'YOURTABLE_2')
group by tablename
order by 1
Regards,
Jan
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
'YOURTABLE_1' , ' YOURTABLE_2' worked fine
Thank you
PS... just to be difficult, what if I required those who had edit right to both not just either?
Barry
Thank you
PS... just to be difficult, what if I required those who had edit right to both not just either?
Barry
Re: Edit rights
use
AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
Great Thank you
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
Hi Pascal.
No, AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
Didn't work.
I have edit rights to all table and when I run the sql on a single table it works file but when I use it using your code for two tables I get
" MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 seconds.) "
No, AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
Didn't work.
I have edit rights to all table and when I run the sql on a single table it works file but when I use it using your code for two tables I get
" MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 seconds.) "
Re: Edit rights
oh, sorry, I guess I was to short in my answer.
Try this
where the count(membership_grouppermissions.groupid) = 2 needs to match number of tables you are putting in the ('YOURTABLE_1','YOURTABLE2')
Try this
Code: Select all
select group_concat(membership_grouppermissions.tableName ORDER BY membership_grouppermissions.tableName SEPARATOR ', ') as tablename, membership_users.memberid FROM `membership_grouppermissions` left JOIN membership_users on membership_users.groupID=membership_grouppermissions.groupID where membership_grouppermissions.allowEdit and tableName in ('YOURTABLE_1','YOURTABLE2') group by membership_users.memberid HAVING count(membership_grouppermissions.groupid) = 2
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Edit rights
Perfect,
It is me how should be sorry for my lack of knowledge. I just replaced the line in Jan's code with the line you supplied.
My thanks goes out to the both of you,
Barry
It is me how should be sorry for my lack of knowledge. I just replaced the line in Jan's code with the line you supplied.
My thanks goes out to the both of you,
Barry