So right now, there is a database with the types of users (this is just an example):

+---+-----------+
|ID |    TYPE   |
+---+-----------+
| 1 | volunteer |
| 2 |  student  |
| 3 |   staff   |
| 4 |  official |
+---+-----------+

+---+------+------+
|ID | TYPE | NAME |
+---+------+------+
| 1 |  1,2 | John |
| 2 |  1,4 | Fred |
| 3 |   3  | Anna |
| 4 |   4  | Erin |
+---+------+------+

+---+--------+----------+
|ID | ACCESS | FILENAME |
+---+--------+----------+
| 1 |  1,2,3 | fs01.pdf |
| 2 |   2,4  | fs02.pdf |
| 3 |    4   | li01.doc |
+---+--------+----------+

7/22/2009 11:18:12 AM

Quote :

"I know it's not the best way, but it's the only way I can think of that will require very little database maintenance later on when they may or may not have someone to volunteer to do this sort of thing"

yeah that's certainly not the prescribed method of storing this kind of data. i'm not sure why doing it the right way would be more or less db maintenance later on. seems like doing it the wrong way is gonna cause more problems. especially if you're writing a form to admin this data, doing it the way you are proposing seems like a waste

[Edited on July 22, 2009 at 11:29 AM. Reason : i r volunteer + official. that sounds about right]

7/22/2009 11:23:28 AM

If the user types are pretty well fixed (i.e. you're not constantly defining new ones) and permissions are inherited (e.g. staff users have access to all volunteer- and student-level files), bitwise operations would probably be faster.

How many users and files are you working with? Might be a good idea to create indices of files by access level.

[Edited on July 22, 2009 at 11:33 AM. Reason : ...]

7/22/2009 11:31:37 AM

Well, one of the problems is that the permissions are NOT inherited. For example, students and volunteers will share liability waivers, but volunteers will not be able to see the email contact list for the students. I assume that the more correct way of doing this would be like:

+---+------+-----+-----+-----+-----+
|ID | NAME | VOL | STU | STA | OFF |
+---+------+-----+-----+-----+-----+
| 1 | John |  1  |  1  |  0  |  0  |
| 2 | Fred |  1  |  0  |  0  |  1  |
| 3 | Anna |  0  |  0  |  1  |  0  |
| 4 | Erin |  0  |  0  |  0  |  1  |
+---+------+-----+-----+-----+-----+

7/22/2009 11:43:39 AM

Use one unsigned 32-bit integer to store the access levels as bit flags, and boom, you can support 32 different access levels. One value covers all possible permutations of a given user's access without worrying about multiple columns. The trick is that your scheme for assigning values to the access levels has to be clear and consistent. The upshot is that you can use bitwise operations to pretty quickly check whether the user has access to a given file.

You can use another table to store which access levels are assigned to which values, e.g. VOLUNTEER is 1, STUDENT is 2, STAFF is 4, OFFICIAL is 8, and so on for the value of each bit in the integer. If you use forms for CRUD when modifying the access levels, no one else should have to worry about making sense of this implementation.

In case it's not clear, you would flag the files' access levels the exact same way.

Here's a more detailed example:

vertigo is a user with VOLUNTEER and OFFICIAL permissions in my scheme. The value of the integer storing his access level in the database is therefore binary 1001, or decimal 9.

vertigo wants to access the lunch menu, which is a document restricted to STAFF. The valuae of the integer storing the file's permissions in the database is therefore binary 0100, or decimal 4.

How do we check whether vertigo can access the file? A simple AND operation comparing his permissions to the file's permissions. 1001 && 0100 returns 0, or false. vertigo cannot read the lunch menu.

Does that make sense?

[Edited on July 22, 2009 at 12:03 PM. Reason : ...]

7/22/2009 11:54:25 AM

Table: Users
+---+------+
|ID | NAME |
+---+------+
| 1 | John |
| 2 | Fred |
| 3 | Anna |
| 4 | Erin |
+---+------+


Table: UserRights
+-------+--------+
|UserID | TypeID |
+-------+--------+
| 1     | 1      |
| 1     | 2      |
| 2     | 1      |
| 2     | 4      |
| 3     | 3      |
| 4     | 4      |
+-------+--------+

7/22/2009 11:59:17 AM

^
correct

7/22/2009 12:14:52 PM

^^yep. Lookup table is by far the easiest to maintain and expand.

Bitwise compare would definitely be the fastest, but is also the most brittle over time. And I doubt the perf difference is going to matter here at all.

7/22/2009 2:02:16 PM

Ah, okay. That lookup table setup definitely seems like the easiest thing to do and wouldn't really require much work to convert what's already there since the databases aren't really populated, yet. Could anyone answer my original question for the sake of teaching a n00b, anyway? I'd rather go with the best practices option if for no other reason than learning correctly, but since I'm still pretty new to PHP and MySQL, I was hoping that perhaps someone might be willing to give me an example of how to do what I was originally looking for.

Thanks!

7/23/2009 10:07:02 AM

So again: Indices of files by access level?

7/23/2009 10:17:38 AM

^^ there might be a way to do it, but even if there is, it's not worth your time wondering about it.

for sake of learning, here's the mysql doc for string functions. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

[Edited on July 23, 2009 at 10:27 AM. Reason : iow, move on and solve real problems. ]

7/23/2009 10:24:43 AM

^^ I suppose I don't understand your question. When I originally started doing this, Google gave me a lot of results where people were just taking their checkbox results and inserting them into their tables as comma-separated strings. I'm not saying that I think they were right, but it certainly seems easy and I still don't quite understand how it's bad, per se. But I see the logic and common sense of doing it the way qntmfred suggested, so I'll do that. I don't see the logic of yours, FroshKiller, but that's because I don't understand what you're talking about. Since this isn't really my profession, and while I do want to do it correctly, I don't really want to make it more complicated and so I'll go with qntmfred's suggestion.

But there are a lot of pages on other forums telling people how to insert checkbox values as a comma-separated value in a table. To me, this makes sense: I have two arrays (one for the users access and one for the files access) and all I want to do is compare the content of the two for any matches. When a file is requested, the file's access array is pulled and compared against the user's access array (which is stored to a session variable when the user logs in) and if there is a match, then they are allowed to view the requested file. I don't really understand what you mean by "indices of files by access level".

I realize that if this were my job, I would probably want to get some decent education before doing any more, but it isn't. It's a volunteer thing. They get me for free and I put in the time that I can. If I can do it right, I will, as long as it doesn't take too much time. If the "right" way does, then I'll go with the "not wrong but not preferred way". In this case, I just want to know how to compare two arrays for matches (without running a second SQL query, if possible). I do very much appreciate any and all advice and help.

Thanks!

^ Well, I suppose I thought it might be an easy and obvious thing. If not, then I'll give up on it. But what's the point of putting an array (or comma-separated values string) into a database to begin with? What would your query look like to insert those different checkbox values into their own table, if you don't put them in together? What I mean is that I have this right now:

$types = implode(",",$_POST['types']);

7/23/2009 10:33:29 AM

Well, what I'm saying is that rather than check against your master table of files, you could create indices for each access level. You'd essentially have a table pointing to files available to access level 1, a table for those available to access level 2, etc.

Whenever the user requests a file, you then check the indices of files available to his access level to see if that file exists in an index. If it doesn't, well, he doesn't have access. If you use the indices to provide lists of files he can select from, a user without access to a file will never even see it in the first place.

7/23/2009 11:05:45 AM

^^well if you really wanted to go down that road you would do something along the lines of

http://stackoverflow.com/questions/1096679/can-mysql-split-a-column
http://stackoverflow.com/questions/1077686/is-there-something-analogous-to-a-split-method-in-mysql

but again, i encourage you to not do it this way. it might be a little bit more work to insert multiple rows and do the joins, but if you start storing data as strings, you're throwing away the primary benefit of using a relational database. you'd probably have to write custom stored procedures for every type of query you want to do against your file access and user type. it'll be very brittle code and will generally have slower performance

[Edited on July 23, 2009 at 11:06 AM. Reason : .]

7/23/2009 11:06:09 AM

Figuring out which approach will work best for your needs really depends entirely on how many users you plan to support, how many concurrent sessions you expect to have, how many files you're going to keep track of, and how many access levels there are.

7/23/2009 11:10:55 AM

I don't know how many users or access levels they're planning on having. The organization is pretty new and this isn't really an integral part of it, yet. Really, it's for record-keeping purposes, but not records that they're required to have (so that if they're a mess or this doesn't work out, it's not going to affect them in a way that will get them in trouble financially or legally or anything).

I agree that it's messy and poorly planned, but it's not awful. There is no single person or core group of people making the decisions for how this will be set up, so I'm doing it myself from what I understand they'll need. That's why I'm trying to be as flexible as possible in a way I can understand. FroshKiller, I finally understand what your method entails. However, I think that, for right now, I'd rather do a lookup table because I feel like I understand it better.

Can someone give me an example of what the SQL query might look like? Brief Googling isn't giving me any examples of how to take a list of checkboxes (an array by default, right?) and insert those individual values into a table. In fact, the more I think about it, there's going to need to be three queries - the first one to insert the document information into the files table, assign it an unique ID, a second query to get the ID of the new file, and a third query to insert the checkbox values into the lookup table. The first two queries are easy, but how do I take that array and insert all values? A while loop?

Thanks for all of the advice, guidance, and help...I sincerely appreciate it!

7/24/2009 9:24:18 AM

you can do multiple inserts with one mysql query like this

INSERT INTO UserRights (UserId, TypeId) VALUES (1, 1), (1, 2), (2, 1), (2, 4), (3, 3), (3, 4)

so in your php you can constuct such a string from your checkbox values and execute one sql query. rather than doing a while loop over each row you'd want to insert like this

INSERT INTO UserRights (UserId, TypeId) VALUES (1, 1)
INSERT INTO UserRights (UserId, TypeId) VALUES (1, 2)
INSERT INTO UserRights (UserId, TypeId) VALUES (2, 1)
etc

7/24/2009 9:34:07 AM

something along the lines of

$fileid = mysql_insert_id();
for($i=0; $i<count($_POST["checkboxname"]); $i++) {
    $query = "INSERT INTO lookuptable (fileid,access) VALUES ($fileid,'".$_POST["checkboxname"][$i]."')";
    $result = mysql_query($query);
}

7/24/2009 9:34:17 AM

i don't know how you're comparing those databases, but i just came across something today that made me think of this thread...if you were to query both databases (your user's rights table and your files access table) and create arrays from the query results (i'm assuming you're using the lookup table method)...then just use a foreach loop to compare the array contents and return a true/false:

$array1 = array(1,2,3,4,5);
$array2 = array(1,3,5,7);
$access = false;

foreach($array1 as $value) {
    if(in_array($value,$array2)) {
        $access = true;
    }
}

if($access) {
    echo "You have access.\n";
}

7/30/2009 9:55:22 AM

gonna nitpick at you here

Quote :

"both databases tables "

that approach def works, but there are certainly advantages to doing a single query to get all the relevant data at once.

7/30/2009 1:32:06 PM

7/30/2009 1:33:56 PM

^^^^ btw you should NEVER use code like that in your actual implementation. That's just begging for injection/overflow exploits.

Use a library for database access, it will be more secure, easier to maintain and a hell of a lot easier to develop with.

^^^ Or you could just use an index table like was recommended before, and do the join on the DB query, and save yourself the coding and the monstrous performance hit.

[Edited on July 30, 2009 at 8:10 PM. Reason : .]

7/30/2009 8:09:30 PM

^or just try mysql_real_escape_string();

7/30/2009 8:16:09 PM

^that only stops one style. querying on a single, common named variable is cruisin for a bruisin.

7/30/2009 8:30:25 PM

Quote :

"And I am now your cruise director. And my name is Captain Bruisin'."

7/30/2009 9:06:52 PM