Extracting data from a string
Posted 03 August 2006 - 04:22 PM
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:
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.
Posted 03 August 2006 - 04:30 PM
$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 to get the first on which will be 2
$userGroup to get the secound etc.
Rember arrays start from zero, so if you want to get the 6th usergroup use $userGroup
Posted 03 August 2006 - 04:41 PM
- 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
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:
The users_table will be the table you already have. The groups_table will be the unique IDs and names of all groups in the database. The group_relations 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 group_relations 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.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users