Extracting data from a string

[Liquid Fire]

ok here is the question:

I am going to have a database for a user that will store which user group(s) this user is part of.  Since i want the user to be able to be part of as many user groups as they want i figure i would make it a large varchar, like varchar(100), i don't see someone really needing to be in more that 20-30 user groups for anything.  want i am thinking is to store soemthing like this in it:

2;5;14

so this user would be part of user groups wiht the id of 2, 5, and 14.  what i need to know how to do it go through the string and get the numbers, which i will most likely put into a array for whatever use i might need it, and ignore the ";" char.  How is this possible?

Also if you think there is a better way to store the user groups in the database, which is MySQL, please let me know.

[wildteen88]

If you ahve string like so:
$var_string = "2;5;14;18";

you can use a function called explode which will get each number into an array like so:
$userGroup = explode(";", $var_string);

Now when you want to acces the usergroup numbers you use:
$userGroup[0]  to get the first on which will be 2
$userGroup[1] to get the secound etc.

Rember arrays start from zero, so if you want to get the 6th usergroup use $userGroup[5]

[HeyRay2]

Placing a delimited list of groups in your user table invites data anomalies. For instance, if you deleted a group then the user could be tied to a non-existant group. To avoid this, you would have to:

[list]
[*]Parse through each user
[*]Extract the list of group IDs for that user
[*]Break the list of group IDs into an array
[*]Remove the group ID from the array
[*]Group the remaining IDs back into a delimited list
[*]Submit the change the back to the database
[/list]

Normalizing your database, and breaking this information into a new table would be more consistent and easier to manage changes.

In this instance, you would need 3 database tables, with structures similar to the following:

[b]users_table[/b]
user_id
user_name
...etc...

[b]groups_table[/b]
group_id
group_name
...etc...

[b]group_relations[/b]
relation_id
user_rel_id
group_rel_id

The [b]users_table[/b] will be the table you already have. The [b]groups_table[/b] will be the unique IDs and names of all groups in the database. The [b]group_relations[/b] will be a list of User IDs and the Group IDs that are associated. This will allow you associate an unlimited amount of groups to one user, and unlimited users to a group.

Additionally, when you remove a group or a user, you'll only need to run one small query to remove all entries in [b]group_relations[/b] that pertain to that given user or group.

The one big hurdle in this method is working with JOINS, which can take a little bit of effort to learn to use correctly. However, once you learn this skill, you'll find that your MySQL queries are MUCH more powerful.

[HeyRay2]

The MySQL manual on JOINS is located here:

[url=http://www.mysql.org/doc/refman/4.1/en/join.html]http://www.mysql.org/doc/refman/4.1/en/join.html[/url]