CODEWITHSUNDEEP
phpmysqlsql
Becky
Becky

Reputation: 2289

Issue with JOIN sql statement

I have two database tables I am attempting to join (home_comments and profile_img). What I am trying to attempt is matching the user_id from the home_comments database to the profile_img database, in order to get the profile image from the person who commented. The query I wrote is not broken, as it is producing results, however, the queried results are showing all of the images each user_id has in the profile_img database.

Whenever a person uploads a profile_img, I just add it to the database and then simply call for the last one within it to display the profile_img. So, I am looking for the same concept to be implemented. The user's last profile_img to show.

Whenever I add LIMIT 1 to the end of the query it only allows one comment to show, total. All I need is for the last (DESC) profile_img to show for each user, but still showing all of the comments. How could I accomplish this?

Initially I had the following query:

$select_comments_sql = "
    SELECT * 
    FROM home_comments
    ORDER BY id DESC
";

My attempt at the query

$select_comments_sql = "
    SELECT c. *,
    p.user_id, p.img
    FROM home_comments AS c
    INNER JOIN profile_img AS p
    WHERE c.user_id = p.user_id
    ORDER BY id DESC
";

Database setups:

home_comments

id, user_id, username, comment, date

profile_img structure:

id, user_id, img

Edit: I also just noticed that it is showing the same comment for each different photo in the database, so duplicated comments. Not sure if this has to do with the image issue.

SQL with query:

$select_comments_sql = "
    SELECT c.*, p.user_id, p.img
    FROM home_comments AS c
    INNER JOIN (SELECT max(ID) as ID, user_ID  
                FROM profile_img 
                GROUP BY user_ID) PI
      on PI.User_ID = C.USer_ID
    INNER JOIN   profile_Img p
      on PI.user_ID = P.User_ID 
     and PI.ID = P.ID
    ORDER BY C.id DESC
";
  if ($select_comments_stmt = $con->prepare($select_comments_sql)) {
        //$select_comments_stmt->bind_param("s", $user_id);
        $select_comments_stmt->execute();
        if (!$select_comments_stmt->errno) {
            //echo "error";
        }
        $select_comments_stmt->bind_result($comment_id, $comment_user_id, $comment_username, $home_comments, $comment_date, /*$commenter_id,*/ $commenter_user_id, $commenter_img);

        $comment_array = array();
        while ($select_comments_stmt->fetch()) {
            $comment_array[] = $comment_user_id;
            $comment_array[] = $comment_username;
            $comment_array[] = $home_comments;
            $comment_array[] = $comment_date;
            /*$comment_array[] = $commenter_id;*/
            $comment_array[] = $commenter_user_id;
            $comment_array[] = $commenter_img;
            $commenter_img = '<img class="home-profile-pic" src=" '.$commenter_img.'">';
            if ($home_comments === NULL) {
                echo 'No comments found.';
            } else {
                echo $commenter_img;
                echo $comment_username. "<br>";
                echo $home_comments. "<br><br><br>";
            }

Upvotes: 2

Views: 63

Answers (3)

MonkeyZeus
MonkeyZeus

Reputation: 20737

I would imagine this should work but I have not tested it.

SELECT c.*, p.user_id, p.img, MAX(p.id) /* not sure if you need MAX(p.id) but try it with/without it here */
FROM home_comments c
LEFT OUTER JOIN profile_img p on p.user_id = c.user_id
WHERE p.id = MAX(p.id)
ORDER BY c.id DESC

Upvotes: 0

xQbert
xQbert

Reputation: 35323

One approach would be to use an inline view to generate a set of data consisting of the max ID for each user's photo then use this set to limit the photos for all users..

In the below I generate a dataset called PI containing the max ID of each profile image for each user. Then this is joined back to profile_img entire set to act as a limit; thus only returning the most recent photo for a user. This in essence ensures you have a 1-M relationship between comments and Photo_image. The many-to-many was what was causing the problem.

SELECT c.*, p.user_id, p.img
FROM home_comments AS c
INNER JOIN (SELECT max(ID) as ID, user_ID  
            FROM profile_img 
            GROUP BY user_ID) PI
  on PI.User_ID = C.USer_ID
INNER JOIN   profile_Img p
  on PI.user_ID = P.User_ID 
 and PI.ID = P.ID
ORDER BY C.id DESC

Upvotes: 2

calcinai
calcinai

Reputation: 2617

If you're only running the query for one (or a few) users, the fastest way is probably to have a sub-select in your select instead of using a join, since you're not strictly 'joining' the data.

SELECT c. *,
(
    SELECT p.img 
    FROM profile_img AS p
    WHERE p.user_id = c.user_id
    ORDER BY p.id DESC
    LIMIT 1
) AS img
FROM home_comments AS c
WHERE c.user_id = x

This will avoid computing every user's most recent profile image when you're only ever fetching one (or a few), since having a nested query won't be able to join on an index.

Upvotes: 1

Related Questions