I have 3 related tables.
- Users, in which I get the following columns (username).
- Submissions, I get the columns (id, title, thumbnail and rate). It also has the user_id column to know which user made the upload.
- Favorites, I get the 3 columns (date, id_submission, id_user)
The thing is, I'm looking at a user's profile and get their ID, and I'd like to show the submissions they've marked as favourites. It more or less works, but not quite right.
SQL code:
The variable $user[0]["id"]
is the ID of the user you are viewing.
$query = "SELECT f.date, s.id, s.title, s.rate, s.thumbnail, u.username
FROM favorites AS f
INNER JOIN submissions AS s
ON f.id_submission = s.id
INNER JOIN users AS u
ON f.id_user = ".$user[0]["id"]."
ORDER BY f.date DESC LIMIT 6";
With user A I have marked a submission from user B as a favorite. But when I am viewing the profile of user A, it shows me the same submission twice.
Something is wrong with the SQL but I'm not quite sure what. I always make queries inside loops, but I'm trying to improve them and try to make them in one to improve performance.
I think it's better if you remove the condition from the inner and put it in a where: