Working with dynamic checkboxes/radio buttons

PHP
1

Hi,

Up till now I’ve processed sets of checkboxes/radio buttons that have been generated from database rows a particular way, but feel its restrictive in some cases and would like to know what others do.

Say I have a form for adding or editing a page with a list of checkboxes to allow certain groups access - Administrator, moderators and members for arguments sake. These groups are stored in a MySQL table, with the checkboxes generated using code like:


#Output group checkboxes
$list = '';
$result = mysql_query('SELECT * FROM groups');
while ($row = mysql_fetch_object($result)) {
   $list .= '<input type="checkbox" name="groups[]" value="'.$row->groupID.'">';
}
echo $list;

I like to store the selected groups in a separate table to maintain relationships and for scalability, in this case the table contains 2 columns - pageID and groupID.

If I’m adding a page, my processing code tends to be like this:


#Add page details...
$id = mysql_insert_id();
#Add group permissions to database
$sql = array();
foreach ($_POST['groups'] as $groupID) {
   $sql[] =  "('$pageID', '$groupID')";
}
mysql_query("INSERT INTO page_groups (pageID, groupID) VALUES ".implode(',', $sql);

If I’m editing an existing page, I tend to delete all the groups entries for selected page then insert again:


#Edit page details...
$id = $_GET['id'];
#Remove all groups for selected page
mysql_query("DELETE FROM page_groups WHERE pageID = '$id'");
#Add group permissions to database
$sql = array();
foreach ($_POST['groups'] as $groupID) {
   $sql[] =  "('$pageID', '$groupID')";
}
mysql_query("INSERT INTO page_groups (pageID, groupID) VALUES ".implode(',', $sql);
#Optimise tables...

Note these PHP codes are simplified; my actual codes guard against SQL injections, etc.

They’ve been OK up till now, but they do have their flaws - being unable to identify changes when editing for one.

With all this in mind, what does anyone else do in situations like this? I have no doubts there are better approaches, so would appreciate some ideas.

Thanks in advance.

2

I’m afraid I do it virtually the same way you do because my groups are dynamic and can contain groups themselves. I also lock the tables when I make changes since I first delete then insert like you. It’s not impossible to identify and record changes, it’s just more work since you have to get a current list of permissions, then compare it with the new list, then record the changes, then update the permissions.

If you have a static number of groups that is unlikely to change and scalability ISN’T a BIG concern, you could use binary arithmetic and store the group access in an integer of binary 1’s and 0’s, or, for readability’s (and scalability’s) sake, in a string of 1’s and 0’s. That makes it much easier to record and identify changes and you can record the access list in a single column within the pages table instead of its own table, but it makes it a bit more difficult if you need to modify the list of available groups. It’s also not exactly considered good database design… just a creative alternative.

3

Thanks for the reply smallshot.

I try to avoid static groups where possible, so I can keep form logic separate from templates. The database, functions and templates tend to be in MVC structure of sorts. I haven’t taken advantage of table locks yet - no idea why, they work really well.