Jump to content


Photo

Extracting data from a string


  • Please log in to reply
3 replies to this topic

#1 Liquid Fire

Liquid Fire
  • Members
  • PipPipPip
  • Advanced Member
  • 806 posts

Posted 03 August 2006 - 04:22 PM

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.



#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 03 August 2006 - 04:30 PM

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]

#3 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 03 August 2006 - 04:41 PM

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:

  • 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:

users_table
user_id
user_name
...etc...

groups_table
group_id
group_name
...etc...

group_relations
relation_id
user_rel_id
group_rel_id

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.

#4 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 03 August 2006 - 04:44 PM

The MySQL manual on JOINS is located here:

http://www.mysql.org....1/en/join.html




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users