Paul Paul - 12 days ago 9
MySQL Question

SELECT query case causing an issue

I have the following query that works perfectly except for one thing. It checks if a profile image is not in the

profile_img
database for the selected user and if not, it gives a default image. Again, this is fine. My issue is, if the user does not have a profile img then I cannot access their
user_id
for some reason. Their username is correct, though. However, if the user does have a profile_img then the link,
echo '<a href="profile?user='.$comment_user_id.'">
, works perfectly fine.

Anyone have any idea how I can get this to work for user's without a profile_img?

SELECT c. *, PI.user_id, case when p.img <> '' and p.img is not null then p.img else 'profile_images/default.jpg' end img
FROM home_comments AS c
LEFT JOIN (SELECT max(id) as id, user_id
FROM profile_img
GROUP BY user_id) PI
on PI.user_id = c.user_id
LEFT JOIN profile_img p
on PI.user_id = p.user_id
and PI.id = p.id
ORDER BY c.id DESC


Code I am trying to call it with

$comment_user_id = $comment_row['user_id'];
$comment_username = $comment_row['username'];
echo '<a href="profile?user='.$comment_user_id.'"><div class="comment-post-username">'.$comment_username. '</div></a>';


Database Tables

home_comments
CREATE TABLE `home_comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`comment` text COLLATE utf8_unicode_ci NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
)

profile_img
CREATE TABLE `profile_img` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`img` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
)

Answer

You are using PI.user_id to get the userid, but this is a field from a left join to the image so it will be null.

Use the user_id in home_comments instead.

If you just take out PI then the wildcard in C should work or use a list of field names (always better.

SELECT C.user_id,   -- or C.*, 
       case when p.img <> '' then p.img else 'profile_images/default.jpg' end img
FROM home_comments AS c
LEFT JOIN (
    SELECT max(id) as id, user_id 
    FROM profile_img 
    GROUP BY user_id
) PI on PI.user_id = c.user_id
LEFT JOIN profile_img p on PI.user_id = p.user_id and PI.id = p.id
ORDER BY c.id DESC

TEST 2

SELECT C.*, 
       case when p.img <> '' and p.img is not null then p.img else 'profile_images/default.jpg' end img
FROM home_comments AS c
LEFT JOIN (
    SELECT max(id) as id, user_id 
    FROM profile_img 
    GROUP BY user_id
) PI on PI.user_id = c.user_id
LEFT JOIN profile_img p on PI.user_id = p.user_id and PI.id = p.id
ORDER BY c.id DESC
Comments