I have a scenario where upon a person registering a new community I want them to automatically become a member of that community.
I have 3 tables:
users
id | user_email | user_password | user_name
communities
id | name | code | admin
community_playes
community_id | player_id
Upon the community being created I immediately need to run the following MySQL queries so that person becomes a member of said community:
1) To get community_id from table communities where admin = email and put into table community_players, community_id
2) To get id from table users where user_email = email and put into table community_players, player_id
I already have email as a variable.
My aim is to populate the community_players table with a new record containing the id of the user, and the community_id of the newly created community.
What would be the SQL statement for this? I have no experience in SQL.
