So I in my infinite free time decided to practice PHP prog by making a
groups database, where members can join groups, etc, much like Google
groups

here is my dilemma: how to handle the associations between users and
groups

here is how I have it set up:

One table users one for groups

Groups table has a column (TEXT format) named members

in this members column there is a text file that contains all the
members that belong to that group, the members are separated by ";" for
parsing

so it looks like: "john;cat;mavis;adam;snoop
dogg;peter;kevin;steve;bob"

is there a way to remove one element from this array (list)? with a
mysql command?

let's say I want to remove "mavis"

how would I go about doing this?

I figured that if I used "UPDATE" then it will do a block update, this
could take long with thousands of members

I also figured that a cheat way to doing this, with using more
resources, would be to create a table for each group with each user
being a separate row.

with hundreds of groups, would this be possible?

On 22 Dec 2006 12:37:16 -0800, phmagic@gmail.com wrote:
> So I in my infinite free time decided to practice PHP prog by making a
> groups database, where members can join groups, etc, much like Google
> groups
>
> here is my dilemma: how to handle the associations between users and
> groups
>
> here is how I have it set up:
>
> One table users one for groups
>
> Groups table has a column (TEXT format) named members
>
> in this members column there is a text file that contains all the
> members that belong to that group, the members are separated by ";" for
> parsing
>
> so it looks like: "john;cat;mavis;adam;snoop
> dogg;peter;kevin;steve;bob"
>
> is there a way to remove one element from this array (list)? with a
> mysql command?

No. Because that is a bad design. Re-do it.

> let's say I want to remove "mavis"
>
> how would I go about doing this?

Outside application. Perl's nice. Well, for "evil and trap-ridden"
values of nice, but it'll do the job in 87 ways if you're careful and
know what you're doing.

> I figured that if I used "UPDATE" then it will do a block update, this
> could take long with thousands of members

Yup. Which is why the design is crap.

> I also figured that a cheat way to doing this, with using more
> resources, would be to create a table for each group with each user
> being a separate row.

That is, however, the right way to do it. And that will be FAST to
remove "mavis" from all the entities to which she belongs.

> with hundreds of groups, would this be possible?

That would be one statement in SQL, and take a tiny fraction of a second
to run.

--
7. When I've captured my adversary and he says, "Look, before you kill me,
will you at least tell me what this is all about?" I'll say, "No." and
shoot him. No, on second thought I'll shoot him then say "No."
--Peter Anspach's list of things to do as an Evil Overlord

phmagic@gmail.com wrote:
> So I in my infinite free time decided to practice PHP prog by making a
> groups database, where members can join groups, etc, much like Google
> groups
>
> here is my dilemma: how to handle the associations between users and
> groups
>
> here is how I have it set up:
>
> One table users one for groups
>
> Groups table has a column (TEXT format) named members
>
> in this members column there is a text file that contains all the
> members that belong to that group, the members are separated by ";" for
> parsing
>
> so it looks like: "john;cat;mavis;adam;snoop
> dogg;peter;kevin;steve;bob"
>
> is there a way to remove one element from this array (list)? with a
> mysql command?
>
> let's say I want to remove "mavis"
>
> how would I go about doing this?
>
> I figured that if I used "UPDATE" then it will do a block update, this
> could take long with thousands of members
>
> I also figured that a cheat way to doing this, with using more
> resources, would be to create a table for each group with each user
> being a separate row.
>
> with hundreds of groups, would this be possible?

Read up on many to many relationships.
Basically, you just need a pivot table that looks something like this:

groups_members(group_id*,member_id*)

* = PRIMARY KEY

then you just have a new row for each relationship