Avatar user in last arguments

Question

I have a phpBB forum and I would like to add users avatar to the database query in the last arguments, I have already created the code that extracts the last arguments, but I would also like to have the user avatar who opened the topic.

this is the code

The PHP file

$sql = 'SELECT forum_id, topic_id, topic_title, topic_time, topic_views, topic_poster, topic_posts_approved, topic_first_poster_name, topic_first_poster_colour, topic_last_post_id, topic_last_poster_name, topic_last_poster_colour, topic_last_post_time, topic_last_view_time, topic_last_poster_id
    FROM ' . TOPICS_TABLE . '
    WHERE ' . $this->db->sql_in_set('forum_id', $flast) . '
    AND ' . $this->content_visibility->get_visibility_sql('topic', 'forum_id') . '
    ORDER BY topic_last_post_time DESC';
$result = $this->db->sql_query_limit($sql, $this->config['total']);

while ($row = $this->db->sql_fetchrow($result))
{
$this->template->assign_block_vars('topic', array(
        'LAST_TOPIC'      => append_sid("{$this->phpbb_root_path}viewtopic.$this->phpEx", 'f=' . $row['forum_id'] . '&t=' . $row['topic_id']),
        'U_LAST_TOPIC'   => append_sid("{$this->phpbb_root_path}viewtopic.$this->phpEx", 'f=' . $row['forum_id'] . '&p=' . $row['topic_last_post_id'] . '#p' . $row['topic_last_post_id']),
        'LAST_POSTER'     => append_sid("{$this->phpbb_root_path}memberlist.$this->phpEx", 'mode=viewprofile' . '&u=' . $row['topic_poster']),
        'USERNAME_LAST'  => append_sid("{$this->phpbb_root_path}memberlist.$this->phpEx", 'mode=viewprofile' . '&u=' . $row['topic_last_poster_id']),
        'TOPIC_TITLE'                   => $row['topic_title'],
        'TOPIC_VIEWS'                   => $row['topic_views'],
        'TOPIC_REPLIES'                 => $row['topic_posts_approved'],
        'TOPIC_LAST_POSTER_NAME'        => $row['topic_last_poster_name'],
        'TOPIC_LAST_POSTER_COLOUR'      => $row['topic_last_poster_colour'],
        'TOPIC_LAST_POST_TIME'          => $this->user->format_date($row['topic_last_post_time']),
        'TOPIC_LAST_VIEW_TIME'          => $this->user->format_date($row['topic_last_view_time']),
    ));
}
$this->db->sql_freeresult($result);

Thanks to your wonderful help

Answer 1

You don't need to store user avatar into database to get the result you described. There is a function doing all the job for you and it is actually used by phpBB itself to display user avatar in e.x. viewtopic page.

In phpBB 3.0:

includes/functions_display.php
function get_user_avatar($avatar, $avatar_type, $avatar_width, $avatar_height, $alt = 'USER_AVATAR', $ignore_config = false)

In newer versions:

includes/functions.php
function phpbb_get_user_avatar($user_row, $alt = 'USER_AVATAR', $ignore_config = false, $lazy = false)

And then it goes like this:

PHP

$this->template->assign_block_vars('last_topic', array(
        ...
        'USER_AVATAR'          => phpbb_get_user_avatar($data),
        ...
    ));
}

HTML

<!-- BEGIN last_topic -->
<li>
{L_POST_BY_AUTHOR}
{USER_AVATAR}
<a style="font-weight:bold; color: #{last_topic.TOPIC_LAST_POSTER_COLOUR};" href="{last_topic.USERNAME_LAST}"> <img src="{BOARD_URL}ext/galandas/lasttopics/styles/all/theme/images/icon_profile.png" height="20" width="20" title="{last_topic.TOPIC_LAST_POSTER_NAME}" alt="" /> {last_topic.TOPIC_LAST_POSTER_NAME}</a> <i class="fa fa-clock-o"></i> {last_topic.TOPIC_LAST_POST_TIME}&nbsp;<a href="{last_topic.LAST_LINK}" title="{last_topic.TOPIC_TITLE}"><span style="color: #BF0000">{last_topic.TOPIC_TITLE}</span></a> <a href="{last_topic.U_LAST_TOPIC}" title="{L_VIEW_LATEST_POST}"><i class="icon fa-external-link-square fa-fw icon-lightgray icon-md" aria-hidden="true"></i><span class="sr-only">{L_VIEW_LATEST_POST}</span></a> {L_REPLIES}&nbsp;{last_topic.TOPIC_REPLIES}&nbsp;{L_VIEWS} {last_topic.TOPIC_VIEWS}
</li>
<!-- END last_topic -->

---

EDIT

Parameter passed to phpbb_get_user_avatar function should be row from users table, something like this:

$sql = 'SELECT u.*, s.*
    FROM ' . USERS_TABLE . ' u
        LEFT JOIN ' . SESSIONS_TABLE . ' s ON (s.session_user_id = u.user_id)
    WHERE u.user_id = ' . $user_id . '
    ORDER BY s.session_time DESC';
$result = $db->sql_query_limit($sql, 1);
$data = $db->sql_fetchrow($result);

You should adjust it to your code, no one will bring you a plug-and-play solution.