Little bit of Logic needed

Hi

I have a groups table and a users table on my database. Each user on the user table has a user_id (int) and then all their info etc.

The group table has the group id, the group info etc, and group_user_1, group_user_2 ..... group_user_15.

So far i have been able to manually put the users id into the groups table so when i look at the group (php) all the user names are shown etc.

Now i want to create a form for a new person to add a group and a number of different users to this group. How would i go about doing this?

Im thinking i would need, INSERT all the users to the user table first, then pass these values into another INSERT script, which firsts GETS the new user id's of these users and creates a new group with the information and the gathered ids?

Is the best way? Also does that even make sense lol ??

Thanks
AJ

 

Can a user belong to multiple groups? I would say your best bet is to have 3 tables:

1. Users
2. Groups
3. UserGroups

I am not really sure what your question is though. Are you looking for database logic or just business logic?

 

yeah a member can be in many groups which is why i need a seperate group name which stores each member id as well.

My question really is this the best way to add the users and groups to my database ... "Im thinking i would need, INSERT all the users to the user table first, then pass these values into another INSERT script, which firsts GETS the new user id's of these users and creates a new group with the information and the gathered ids?"

 

Ok, so you definitely need 3 tables as I suggested above. So in order to add a user to a group, the group would need to be created first.

So the process would be:

1. INSERT INTO Users (set a variable for the newly inserted user_id)
2. INSERT INTO Groups (set a variable for the newly inserted group_id)
3. INSER INTO UserGroups (user_id, group_id)

 

ok i see how this works.

When a new user is added to my users name it auto-increments a user_id (int). How can i then get this value from the users table and insert it into the UserGroup table, i will need a 3 different php scripts/files for each job correct?

 

To get the last ID inserted, you would use a query like:

SELECT @@IDENTITY AS user_ID FROM users