I'm new to advanced MySQL having only come across many-to-many relationships yesterday. I'm working on a project where users can join multiple projects and projects can accommodate multiple users.
My tables are:
Users - ID, name, email, password etc Projects - ID, name, URL etc
Following advice from this site, I have set up a linking table with two foreign keys matched to the IDs of the above tables:
Users_Projects - Users_ID, Projects_ID
I understand the next step is something to do with joining, but how do I add a user to a table, or see who the members of a project are/what projects a particular user is a member of?
Projects for a given user:
SELECT *
FROM Projects p
LEFT JOIN users_projects up ON p.projects_id = up.projects_id
WHERE up.users_id = [INSERT USERID HERE]
To assign user to project you need to type user id and project id into the User_Projects table, to see which users are in selected project you can type:
SELECT Users.name, Users.email etc.. from Users_Projects JOIN Users on Users.ID =
Users_Projects.Users_ID JOIN Projects ON Projects.ID = Users_Projects.Projects_ID WHERE (Put your where statement here)
Read more on wiki: JOIN wikipedia
I think you forgot an id (in users, create new fields 'projectid' on user table)
ADD USER => INSERT INTO users (name,email,...) VALUES ('fred','redeyes@XX.com',...);
CHECK USER => SELECT name, email FROM USERS;
CHECK USER BY PROJECT ID => SELECT name, email FROM USERS, PROJECTS WHERE USERS.projectid = PROJECTS.id