Joining two tables

[Radon3k]

I have two tables in the database and I would like some help not only setting it up but with the join statement as well.

I have a user table and a usergroup table. I want to have a few different usergroups however within one usergroup (which we'll call the division head usergroup) I want to have almost "sub-usergroups". In other words, you have two users in the division head usergroup I want them to have different permissions. I don't want the division head of hats to see the same thing as the division head of shoes but I want them both in the same user group since they're both divisoin heads. Is this possible to do or will I have to just create multiple usergroups? If it is possible, how do I figure out the query statement which will select the appropriate information baed on which user is the head of shoes vs hats?

Also I've been told I'll need to join the two tables but I'm not sure how. Could anybody assist with that, please? I've searched on the forums and on Google and I've found the code for it but I don't understand why it's structured the way it is or what it means.

Thank you for any help you can provide. 🙂

[devinemke]

i suggest 2 tables: users and groups. here's a basic idea of the data structure (i'm assuming you are using mySQL):

users
user_id (INT)
name (VARCHAR)
email (VARCHAR)
groups (SET '1','2','3')

groups
group_id (INT)
name (VARCHAR)
permissions (VARCHAR)

notice that users.groups is a SET field, meaning that each row can contain multiple groups, it is a list of group_id's that relate to the group table.

then let's say we want to pull all users from group 1:

SELECT * FROM users WHERE group = '1'

or let's say we want to see what groups user #123 is in:

SELECT groups FROM users WHERE user_id = 123

after the above query, you could then see what those permissions are:

SELECT permissions FROM groups WHERE group_id IN(resulst from last query)

[Radon3k]

I'm afraid I still don't understand some of this. Will this take care of different users having different permissions? It looks as if you can set the usergroups to have certain permissions, but not the users themselves, which is one of the big things for me. Just because two people are in the same usergroup doesn't mean they need to see the same things. I want them to see different things depending on which division they are in.

[devinemke]

your post seemed to suggest that your grouping heirachy has a lot of overlap. as you said user1 and user2 are both in the "division head" group but user1 is also in the "hat" group and user2 is the "shoe" group.

so using my example:
the "groups" table would have (at least) 3 records:

group_id = 1
name = division heads

group_id = 2
name = hats

group_id = 3
name = shoes

and the "users" would have (at least) 2 records

user_id = 1
name = Joe Hatsworth
email = [e-mail]
groups = 1,2

user_id = 2
name = Jane Shoester
email = [e-mail]
groups = 1,3

as you can see both Joe and Jane and part of group_id 1 (division heads) but only Joe is also a member of group_id 2 (hats) and only Jane is a member of group_id 3 (shoes). You could then set the permission fields in the group table to control what they both can/can't see. since they are both division heads perhaps they can both see some restricted content but only Joe can see the page detailing the secret 2005 line of hatwear and only Jane can see the super secret shoes.

make sense?

[Radon3k]

Yes, that makes a lot more sense now, thanks! 🙂 My next question would be how is it entered into the database with either a radio/checkbox or a drop down list? I understand now how the database schema should work, which is good because this will help me in future projects. 🙂

[devinemke]

here's the basic idea (i'm not going to write out all the code for you):

1. query the database and grab all of the group id/names. store them in an array (id's as keys, names as values)

2. use an HTML form to enter data for a user (name, email, etc..) and use the group array to generate a set of checkboxes (you would want checkboxes, not radios, because each user can be a member of multiple groups). i suggest coding your checkbox names as an array with the group id's as values, that way when submitted you will have $_POST['groups'] as an array of groups that that user is in. i suppose you could use a multi select drop down but i would use checkboxes.

3. when the form is submitted run an INSERT query to the users table with all of the info (validate first) including a comma separated list of groups to go into the SET field (use the implode() function to do this).

[Radon3k]

Sorry, I didn't mean to imply for you to write the code for me as that's not what I wanted. I was more interested in the actual SQL statement when it came to the SET field type. When you talk about setting the group table into an array, are you talking (in terms of PHP) a mysql_fetch_array function or something else?

Thanks for helping me, I really appreciate it. Again, sorry if you got the impression that I wanted you to write the code for me, I was just curious about the INSERT statement in regards to the SET field type. 🙂

[devinemke]

INSERTing into a SET field is simply setting the field's value to a comma separated list of available SET options:

INSERT INTO table_name SET set_field_name = '1,2,3'

[Radon3k]

For the SET field type, is it a requirement that you fill in every place? If a user only belongs to one usergroup (usergroup 1 for this example) would it just simply be '1','',''?

[devinemke]

Originally posted by Radon3k
For the SET field type, is it a requirement that you fill in every place? If a user only belongs to one usergroup (usergroup 1 for this example) would it just simply be '1','',''?

no, you would simply do set_field = '1'
perhaps you should do a little reading about the SET type