Becky Becky - 1 month ago 4
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

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.