help needed with form and mysql design.

[thoresson]

Hi,

I'm having trouble designing a good html-form/MySQL combination for an access rights system.

I'm having three tables in MySQL: members (with member information for my site), albums (with information for separate albums with digital photos on my site) and accessrights (which should function as a "bridge" between members and albums, controling which albums a specific member could view).

accessrights is defined by the following:

CREATE TABLE accessrights
(
albumid INT,
userid INT,
access ENUM("Y","N") DEFAULT "N" NOT NULL,
UNIQUE (albumid, userid)
);

albumid is from the albums table, and userid from the members table. For each member/album combination I want accessrights to contain one entry with access set to either Y or N.

So far everythings alright. But how do I build a system that lets me control the entries in accessright?

Right now, I'm trying with a form for album editing which contains something like this:

    db_connect($dbuser, $dbpassword, $dbdatabase);
    $query = "SELECT userid FROM members";
    $result = mysql_query($query);
    while($row = mysql_fetch_array($result)) {
        echo($row[0]);
        ?>
        <INPUT TYPE="checkbox" name="<?php echo($row[0])?>" VALUE="YES">
        }

This creates a checkbox for every member on my site. But the problem is that only checked boxes are passed on to the next script, which process the form for album-editing. This means that if I uncheck a box that's been checked before, this information isn't passed along.

From what I understand, information about unchecked boxes is never passed?

Right now, my solution is to first set the field access in the table accessrights to N for every user and then update the table with an Y for those with checked boxes.

But how can I limit the MySQL queries to just include members for which the access field has been changed?

--
anders thoresson

[dale_burrell]

I'm not sure that I completely understand the problem, but yes you are correct, unchecked boxes are not passed, you can detect that they are unchecked by seeing if the Form data for the checkbox is undefined.

As far as your access rights go, I would have thought that a good solution would be to only store bridging records for granted rights. There isn't really any need to store the record when the rights are no - that means your bridging record only requires the UserId and the AccessId, and the existence of the record implies access rights.

Then on your form, you can either have a check box for all possible rights, and then the SQL only cares about checked boxes, so that solves your unchecked box problem. The simpliest method is probably to remove all access right where UserId = CurrentUser and then insert the access rights that are defined in the form. To limit which records are inserted/updated use the where clause: where UserId = UserOfInterest.

Hope that helps...

[thoresson]

I'm not sure that I completely understand the problem

Well. To be honest, I might be the problem myself. Am only two or three months into html/php/mysql...

Then on your form, you can either have a check box for all possible rights, and then the SQL only cares about checked boxes, so that solves your unchecked box problem.

I'm not following you here.

As I done things now, I have a form for editing album settings. In this form I present a list of checkboxes, one for each user in my members-table. I also get there current accessrigths from my accessrights-table.

A checked box means "access granted" for the album being edited. Not check means "access not granted".

I have no problem when the administrator checks and previous unchecked box, since this information is passed along. But if the administrator unchecks a checked box, this information is not passed along and therefore the members still have accessrights to the album, even though the administrator thinks he has changed this.

Is your suggestion to first clear the accessrights-table for the album being edited, and then entering a albumid/userid combination for each member who's checkbox is being checked?

--
anders thoresson

[dale_burrell]

Is your suggestion to first clear the accessrights-table for the album being edited, and then entering a albumid/userid combination for each member who's checkbox is being checked?

Basically yes. If you remove all rights for the given conditions and then add back the new ones - much simplier than trying to work out which ones have changed etc.

Although I suspect you're going to run into problems either way, because at some point you are going to have a huge list of albums and a huge list of members so to display checkboxes for all of them will be a big page!

See you can generate the list of possible rights from your rights table, joined with the existing rights to get whether they are current or not e.g. for a given album, CurrentAlbum

select Name, isnull((select 1 from AccessRights where MembersId = Members.Id and AlbumId = CurrentAlbum), 0) as GotRights from Members

delete from AccessRights where AlbumId = CurrentAlbum

{Obtain the list of CurrentMembers from the checked boxes for this album, build that up in code so that you have a list for the following insert statement.)

insert into AccessRights (AlbumId, MemberId) select CurrentAlbum, CurrentMembers

Hope this helps. This is for SQLServer - can't promise it works for MySQL.