How to select names using link table?

Question

So I have a check to see if you're allowed to view that folder(currently checking if the ip is my home ip)

I want this check updated into checking if your account has the rights. So i want to get all the names from gallery_folders where user_id = :user_id (yes pdo, but that doesn't matter for this)

SELECT      f.name 
FROM        gallery_folders     as f 
LEFT JOIN   gallery_link        as p 
WHERE       f.id = p.folder_id
AND         p.user_id = :user_id

This is where I got stuck, I'm not sure how to add user_id to this so it'll only get all the (p.folder_id where user_id=:user_id)

Databse structure if necessary:

Folders

• gallery_folders
  • id
  • name

Users

• gallery_users
  • id
  • username
  • password
  • token

Link table

• gallery_link
  • id
  • user_id
  • gallery

Answer 1

If you only want the folders where the user has the proper rights then you will want to change the join from LEFT JOIN to INNER JOIN. You also need to fix your join statement so that it is ON f.id = p.folder_id instead of WHERE f.id = p.folder_id.

I think this is what you are after:

SELECT f.name 
FROM gallery_folders f 
INNER JOIN gallery_link p 
ON f.id = p.folder_id
WHERE p.user_id = :user_id