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. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Mayank K. Swami
– Mayank K. Swami, Commented Aug 2, 2013 at 3:38

bblincoe · Accepted Answer · 2013-08-02 03:39:26Z

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 2, 2013 at 3:39

bblincoe

2,5132 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crackertastic Over a year ago

+1 This is what I would do. Simply define a default value in the SQL schema for that column.

Lucio Rubens · Accepted Answer · 2013-08-02 03:44:54Z

1

Define default in column database.

Or...

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

answered Aug 2, 2013 at 3:44

Lucio Rubens

5555 silver badges12 bronze badges

Comments

vee · Accepted Answer · 2013-08-02 03:52:21Z

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 2, 2013 at 3:52

answered Aug 2, 2013 at 3:45

vee

38.7k7 gold badges76 silver badges80 bronze badges

1 Comment

John Roe Over a year ago

Thanks vinodadhikary, exactly what I needed. Perfect!

spencer7593 · Accepted Answer · 2013-08-02 03:44:44Z

0

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 ''.

answered Aug 2, 2013 at 3:44

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Collectives™ on Stack Overflow

MySQL query php conditional

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related