PHP SELECT From Multiple Rows Depend On Criteria?

[Elewa's Bazaar]

Hello everyone,

I want to do something on my website, but I still dont know how to do it yet. I have two tables in my database, one is called users and the other is called groups.

fields in the first table
firstname - lastname - email -birthday - gender - password - user_level - signup_date - last_login - activated - account_type - account_owner_name - account_routing_number - account_number - shares - groupname - numberofmembers - paymentamount -paymentrounds.

fields in the second table
groupname - numberofmembers -paymentamount - paymentrounds - email.

When a user CREATE a group, I insert the group's information in the first table in the same row where his/her information inserted. Also, I send the group's information plus his/her email address to the second table.

I want to know how to do the following:
1) Insert the group's information when someone ENROLL in the group.
2) Select First and Last name of members who are enrolled in the same group.
Yes, I have userid and groupid fields in the tables. I am sorry that I didnt mention that. You idea looks helpful. I will try it once I get to mu computer, and I will let you know.
I created a third table named it users_groups with two fields userid and groupid ... I tried to make a relationship between userid in users_groups and userid in users table and the same relation between groupid fields but it didnt work (When I input any data, the users_groups did not update). Note: I changed the engine to InnoDB and choose CASCADE in both delete and update.

 

[Ratchetr]

Do you have a primary key in those tables? Nothing in the second table looks like a good candidate for a primary key. You really should add id fields to each table that are auto increment primary keys.

So, add userid to first table
Add groupid to second table.

Now you can create a 3'rd table that lists the members in each group:
Fields:
userid - Foreign key to userid in first table.
groupid - Foreign key to groupid in second table.

Adding a member to a group is as simple as:
INSERT INTO users_groups(userid,groupid)
VALUES( $userid,$groupid)

Selecting the names of the users in a group is a simple join:
SELECT firstname,lastname
FROM users
JOIN groups USING(groupid)
WHERE groupid = $groupid

Also, you don't really need to keep a numberofmembers field (it means you need an update statement along with the insert above).

Just count them when you need to know:

SELECT count(*)
FROM user_groups
WHERE groupid = $groupid