使用MySQL / PHP中的关系数据库过滤信息

Question

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.

Answer 1

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