Need Assistance

[Mr President]

I know we have some programmers among us so I'm hoping one of them will be able to help me with an issue I'm having. There is a way and I haven't figured it out just to to keep existing data on a db table but enter more.

Let me explain this in more detail.

Say we have a table called users with a column called groupid and the normal group id = 2 but say I want to allow more groupid's to this field what kind of query would I run to do that with? So in that field it would have 2,12,18.. This would allow that user to be in 3 usergroups...

There is a way to do this as I have seen it.. I just don't know how. If I do an update query it will remove what is there and replace it with the new selection.

Would I have to do a SELECT on what is there then put the result in the VALUE along with the new groupid? I didn't try that yet, but it is the only way I can think of but there has to be an easier way..

Ideas?

[DBozMen]

Well to solve your problem you need to add a new table. It's called a junction table: http://en.wikipedia.org/wiki/Junction_table


This way you don't have to do a lot of weird programming :).


Then you can use a simple subquery to get that result:




SELECT * FROM Groups WHERE group_id IN (SELECT group_id FROM usergroup WHERE user_id = 1)

[Mr President]

I actually figured it out after I made the post..




UPDATE user SET membergroupids = CONCAT(membergroupids, ',X') WHERE userid = Y


It worked pretty well.

[DBozMen]

Allright :-), that works aswell. But i would recomend my solution for a DB with good structure. If it's for the NW game DB, then it doesn't matter. That one isn't the greatest :D

[Mr President]

I do like your idea as well.. And no our structure isn't the best.. lol Either way will do fine.