Posted by thermalmusic on July 24, 2011 at 4:40pm
Hi everybody,
I want to create a select query to return a list of all roles and their related permissions directly from a MySQL database. Does anyone have a good one for that?
Thanks,
Thermal
Categories: permissions, roles, select query
Comments
D7 SELECT * FROM role r left
SELECT * FROM role r left join role_permission rp on rp.rid = r.ridSELECT * FROM role r left join permission rp on rp.rid = r.ridselect permissions and group by role
Thats nice and I can use it. Though any suggestion for returning each permission by role, perhaps using a group by?
such as
role1, perm1
role1, perm2
role1, perm3
role2, perm1
role2, perm2
role2, perm3
etc
Thanks,
Thermal
Are you using d6 or d7 ? I'm
Are you using d6 or d7 ? I'm not sure how this could be done in d6, as perm field is permission table is a longtext; holding permission strings as comma separated value. For d7 the following SQL could work,
SELECT name, permission FROM role r left join role_permission rp on rp.rid = r.rid order by name, permissionD6
I'm using D6. Yes I see that all permissons are strings. Do you know where those separate values are coming from? Probably each module.
Thermal
Hi Thermal
Yes Thermal you are correct.
Those permissions are coming from modules only.Each module will be having its own permission.
And those are returning from hook_perm.
You can check this url hook_perm
Regards,
Veera Prasad Dagudu
www.drup-all.com
Hi
Thanks for your help. I got what I needed.
Thermal
Select query for Users/Profiles on single rows
Hi,
I'm trying to write an Sql query on a D6 database to return users and their profile values. My problem is getting profile values to return as separate fields (columns) in the resulting rows, since profile data is saved in the profile_values table as children, identified by fid.
I've tried Group_concat but it is not a solution. Profile csv module is not sufficient either. I'd like to have an output like this:
User | Firstname | Lastname | Address | City | State | Zip | etc |
If anyone has done this as a query, please let me know.
Thanks,
Thermal
Self joins on profile_values table
The sample sql below will return a single row for each user, where you can add a left join for each additional field you want, and check the fids match the field name you want in the SELECT clause.
SELECT a.uid as User,a.value as FirstName,
b.value as LastName,
c.value as Address
FROM profile_values a
LEFT JOIN profile_values b
ON a.uid = b.uid AND b.fid = 5
LEFT JOIN profile_values c
ON a.uid = c.uid AND c.fid = 7
WHERE a.fid = 4