Becky Becky - 11 months ago 33
MySQL Question

SQL JOIN statement not outputting correct data

I am having an issue with either my SQL makeup or my query. I am building a comment system and I have two databases. I am attempting to

JOIN
the databases and have the query work properly.

What I am attempting to do is get the img from
profile_img
that is associated with the user_id from both tables. However, I only want the last image from the user (within the profile_img database) to show. When I upload profile images, they simply add on and I only output the last one uploaded.

The databases I am using are:

home_comments:

id, user_id, username, comment, date


profile_img structure:

id, user_id, img


My original
SQL
was this

$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
";


This original
SELECT
SQL
works with my query, which is:

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_user_id, $commenter_img);
//var_dump($select_comments_stmt);
$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_user_id;
$comment_array[] = $commenter_img;
$commenter_img = '<img class="home-comment-profile-pic" src=" '.$commenter_img.'">';
if ($home_comments === NULL) {
echo 'No comments found.';
} else {
echo '<div class="comment-post-box">';
echo $commenter_img;
echo '<div class="comment-post-username">'.$comment_username. '</div>';
echo '<div class="comment-post-text">'.$home_comments. '</div>';
echo '</div>';
}
}
}


This produces the user's profile picture for their comment, however, it makes duplicate comments for each profile image the user has.

I now, have this new
SQL
to try and resolve this issue (which it shows it works in an SQL fiddle), but it outputs nothing:

$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
";


So, is there something wrong with my prepared statement/output or is the new
SQL SELECT
statement not correct?

Does anyone see what is wrong with this? Please let me know if you have any questions.

Answer Source

Further to a comment I left:

"The case sensativity did it! Thanks! Feel free to leave an answer. Quick question though - based on the way my query is laid out, how could I add error reporting to it? I am trying to write with PDO, so not sure how to do it with this. – Becky"

As it appears, aliases are case-sensitive, therefore you need to change your query to:

$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
    ";
  • By default, table aliases are case sensitive on Unix, but not so on Windows or OS

Using exceptions/error handling on the query would have signaled an error.