CODEWITHSUNDEEP
phpmysqlsqlselectjoin
user2793987
user2793987

Reputation: 199

SQL Join Favorites List Returns Wrong Name

I'm attempting to do a relatively simple MySQL query where I return the favorite posts added by the user. However, my only error is the name of the post int he favorites returns as the user who added the favorite and not the actual user who wrote the post. Below is my database for the favorites and then the query below. Please let me know if you need more information from me as I'd be happy to help in any way.

enter image description here

$username = $_GET['username'];

//initial query
$query = "SELECT body, latitude, longitude, (SELECT username FROM Users WHERE Users.IDUser=Favorites.IDUser) AS username, (SELECT profile_picture FROM Users WHERE Users.IDUser=Favorites.IDUser) AS profile_picture, filename, post_date FROM Posts, Favorites WHERE Posts.IDPosts = Favorites.IDPosts AND Favorites.IDUser=(Select IDUser FROM Users WHERE Users.username=:username) ORDER BY post_date DESC";

$query_params = array(
        ':username' => $username
    );

DB diagram:

![enter image description here][2]

Upvotes: 0

Views: 68

Answers (1)

mayy00
mayy00

Reputation: 224

You should have uploaded your database schema for this question and you should've formatted your query to be more readable but anyway I don't know if that's correct, I could not tried it but try and please let me know if it is wrong (I don't know what I'm doing because I cannot see the schema and I don't know what are you trying to do)

SELECT body, latitude, longitude, Users.username, Users.profile_picture, filename, post_date FROM Posts, Favorites, Users WHERE Users.username=:username AND Posts.IDPosts = Favorites.IDPosts AND Favorites.IDUser = Users.IDUser ORDER BY post_date DESC

Edit

 SELECT Users.profile_picture, Users.username, latitude, 
        longitude, post_date, filename
 FROM Users 
 JOIN Posts ON Posts.IDUser = Users.IDUser
 JOIN Favorites ON Posts.IDPosts = Favorites.IDPosts
     WHERE Users.username=:username 
           AND Users.IDUser = Posts.IDUser
           AND Posts.IDPosts = Posts.IDPosts

Upvotes: 1

Related Questions