Jump to content

Archived

This topic is now archived and is closed to further replies.

Liquid Fire

Extracting data from a string

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.