MySQL query php conditional

Question

I have a sql query, but I would like to make sure if someone doesn't upload an avatar (avatar_filename), it will display an alternative avatar (/images/avatars/default.jpg).

I've been looking at if conditionals on this website and tried to used them without success.

This is my working query for the moment:

$query =   "SELECT exp_forum_topics.last_post_author_id, exp_forum_topics.title, exp_forum_topics.topic_id, exp_forum_topics.last_post_date, exp_members.member_id, exp_members.screen_name, exp_members.avatar_filename ".
                        "FROM exp_forum_topics, exp_members ".
                        "WHERE exp_forum_topics.last_post_author_id = exp_members.member_id ".
                        "ORDER BY exp_forum_topics.last_post_date DESC ".
                        "LIMIT 4";

$result = mysql_query($query) or die(mysql_error());

   while ($row = mysql_fetch_array($result)) {

    echo '<img src="/images/avatars/';
    echo $row['avatar_filename'];
    echo '" />';

    echo "<h3><a href='/forum/viewthread/";
    echo $row['topic_id'];
    echo "'>";
    echo $row['title'];
    echo "</a></h3>";

    echo "<p>by <a href='/forum/members/";
    echo $row['last_post_author_id'];
    echo "'>";
    echo $row['screen_name'];
    echo "</a></p>";
 }

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://stackoverflow.com/a/14110189/1723893). — NullPoiиteя, Aug 02 '13 at 03:38

score 1 · Answer 1 · answered Aug 02 '13 at 03:39

1

Have you considered setting the field (column) in the table with a default value? e.g. DEFAULT='my-image.jpg' or something similar?

answered Aug 02 '13 at 03:39

bblincoe

2,393
2
20
34

+1 This is what I would do. Simply define a default value in the SQL schema for that column. – Crackertastic Aug 02 '13 at 03:45

score 1 · Answer 2 · answered Aug 02 '13 at 03:44

1

Define default in column database.

Or...

if (!isset($row['avatar_filename']) {
    echo 'default_avatar.png';
} else {
    echo $row['avatar_filename'];
}

answered Aug 02 '13 at 03:44

Lucio Rubens

555
5
12

score 0 · Answer 3 · answered Aug 02 '13 at 03:44

You could handle this in the SQL

in the select list, just replace this:

 exp_members.avatar_filename

with something like:

 COALESCE(NULLIF(exp_members.avatar_filename,''),'default.jpg') 
   AS avatar_filename

That's equivalent to:

 CASE WHEN exp_members.avatar_filename <> ''
      THEN exp_members.avatar_filename
      ELSE 'default.jpg'
 END AS avatar_filename

This essentially emulates the column having a default value of 'default.jpg', by returning that value whenever the column is NULL or is equal to the empty string ''.

vee · Accepted Answer · 2013-08-02T03:52:21.050

0

You could check if the returned value is empty and set your default image as follows:

if (!empty($row['avatar_filename'])) {
  echo '<img src="/images/avatars/';
  echo $row['avatar_filename'];
  echo '" />';
} else {
  echo '<img src="/images/avatars/default.jpg" />';
}

edited Aug 02 '13 at 03:52

answered Aug 02 '13 at 03:45

vee

38,255
7
74
78

Thanks vinodadhikary, exactly what I needed. Perfect! – John Roe Aug 04 '13 at 22:06

MySQL query php conditional

4 Answers4