I'm working on my first project (a project management system) to understand PHP and MySQL better. I currently have 3 tables in my database, one that lists all the projects ('projects' table), one that stores the users username and password ('users' table) and then a third, small 'permissions' table that is meant to filter out what projects a user can see from the projects table.
The third 'permissions' table consists of an unique id, and then a "user_id" and a "project_id" in the other two columns. The "user_id" references the id of the user in the "users" table, and the "project_id" references the id of a project in the projects table.
Note that more than one user can have access to a single project, that's why there isn't just a single column for this in the projects table, and I read that using comma separated lists for the multiple usernames is a bad way to go about it. As such I have set up foreign keys on "user_id" and "project_id" that reference the id of each accordingly.
Now I'm not sure if this is a good way to set things up but it seems okay in my head.
Really what I want to do is retrieve all the projects a certain user has access to. So something kind of like (but obviously works):
SELECT * FROM projects WHERE user_id = 3;
Is there a simpler way to do this? Something to do with the join command but I could never get it right.
Thanks in advance!
EDIT:
Apologies for the ambiguity, gets a bit tricky to explain.
Essentially I just want a way to filter what rows are displayed from the projects table by the user_id in the users table (note again more than one user can access a project so I can just put a "user_id" column in the projects table and leave out the third table altogether).
My tables are pretty much like so:
projects:
|id|project_title|project_notes|project_status|
|1 |A Project |Some Notes |Finished |
|2 |A 2nd Project|Some more... |In Progress |
users:
|id|username|password|
|1 |johndoe |*********|
|2 |benhill |*********|
permissions:
|id|user_id|project_id|
|1 |1 |2 |
|2 |2 |2 |
user_id = id from users table. project_id = id from projects table.
Then in HTML/PHP I only want to retrieve the projects that a user has access too.
Unfortunately your question isn't very clear but I assume you want to have a single query to find all project that a particular user has access to, you can simply join up the tables with the id as such:
SELECT * from projects INNER JOIN permissions ON
projects.id = permissions.project_id WHERE
permissions.user_id = 3
Assume the user you are looking for has id of 3