Paul Paul - 1 month ago 8
MySQL Question

SELECT query check for something outside of database

I have the following query below and it works for the most part, until I just noticed it is causing a slight issue. Basically what the existing query does it select all the information from my 'home_comments' table and then matches the max id from the profile_img table and takes that image.

The issue I am running into is that not all of my users have profile images within this database. For default images, I use a static image. So, is there anyway I can check if the user has a profile_img and if not use this?

$default_profile_img = '<img class="home-profile-pic" src="profile_images/default.jpg">';


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


EDIT: updated SQL

$select_comments_sql = "
//SELECT c. *, p.user_id, p.img
SELECT c. *, PI.user_id, case when PI.img <> '' and PI.img is not null then PI.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
";


Errors:

[27-Oct-2016 13:29:56 America/Chicago] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//SELECT c. *, p.user_id, p.img
SELECT c. *, PI.user_id, case when PI.img <> ' at line 1' in /home4//public_html/.com/account/ajax-php/comment-retrieve.php:36`

Answer

You can use a case statement:

$select_comments_sql = "
    SELECT c. *, p.user_id, case when p.img <> '' and p.img is not null then p.img else 'my-default.png' end 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
Comments