How to generate a user role grid

Question

I have the following tables:

       users (id, username, ... )
       roles (id, name)
 roles_users (user_id, role_id)

I am wondering how I can create a nice sort of user-role-grid from that which an admin can use to administer roles to users in a clear way. What I would like is basically a table full of checkboxes sort of like this:

        Login  Editor  Admin
Alice     ■      ■       ■
Bob       ■      □       □      
Carol     ■      ■       □

                      [Apply]

Generating the table isn't too much of a deal, but I am very unsure how to handle it when it comes to how to name all the checkboxes and especially how to read and update the database in a not too clumsy way. Does anyone have any good advice or pointers on how to do this in a mostly clean way?

I'm using the Kohana 3 framework, if there is anything there that can make this even easier, but I of course welcome any answer.

Answer 1

I did something similar with Kohana2. Using its models for User and Role, setting the roles for a user was as simple as $user->roles = $rolesArray. Then $user->save() knew to update/insert automagically.

A way to name the checkboxes is name="userid[]" value="roleid", so name="1[]" value="2". Those will appear as php arrays in the form submission for each userid. Then you can make them into arrays and assign them to each user object.

Answer 2

1. By default roles table contains both name and description fields, use it to generate checkboxes.
2. I'd preffer changing permissions for one user at once. Something like this:

           Login      Editor    Admin 
   Alice     ■       □     ■     [Apply]

3. ORM in Ko3 doesnt support "magic" HABTM and provide add()/has()/remove() methods to work with Many-to-Many relations. So, you should call remove() first to delete old settings (here is my fix for remove() method to delete all roles at once), and then call add() for each new role. Another way is to iterate through the roles and check each role with $user->has('role', $role).

   Too many queries for me :) You can store original user roles and calculate diffs ($removed_roles and $new_roles) before saving. Use DB Query Builder to delete $removed_roles and add $new_roles (2 queries max).

PS. Yes, my English sucks, I know it ;)