How to fetch user avatar by user ID?

Discussion forum for Extension Writers regarding Extension Development.
User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

How to fetch user avatar by user ID?

Post by Kami-sama » Fri Oct 12, 2018 9:32 pm

Hey!

I was using this amazing EXT: https://www.phpbb.com/customise/db/exte ... ve_topics/
It does everything I need, except it does not show last poster avatar.

I was thinking how to adjust it and seam to be stuck a bit.

So, basically, EXT takes all info from TOPICS table.
Avatar is obviously not stored in there. We can only get last poster ID, name, colour.
So maybe using one of those variables I could fetch avatar from user table? Any suggestions?

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Fri Oct 12, 2018 9:48 pm

user_id can be linked to last_ poster_id and fetch what you want from users table.
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Fri Oct 12, 2018 9:56 pm

GanstaZ wrote:
Fri Oct 12, 2018 9:48 pm
user_id can be linked to last_ poster_id and fetch what you want from users table.
How would you link it?

I have found array where all variables are assigned. SO I guess inside of it I would do sql SELECT, but not sure how to specify the condition to fetch only that specific user avatar. Or would it be something with $event['user_row']? I am so confused ;D

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Fri Oct 12, 2018 10:41 pm

It's hard for any of us who haven't seen the code, to help you. Do you want to modify a specific sql_ary (custom coding or extension listener) or you want to make a new sql_ary?
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Fri Oct 12, 2018 10:45 pm

GanstaZ wrote:
Fri Oct 12, 2018 10:41 pm
It's hard for any of us who haven't seen the code, to help you. Do you want to modify a specific sql_ary (custom coding or extension listener) or you want to make a new sql_ary?
I want to add to existing array of mentioned extension listener. (at least that makes the most sense to me, but I am open to other suggestions) Here is the array from ext listiner:

Code: Select all

//Last active topics
    $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', 'topic') . '
		ORDER BY topic_last_post_time DESC';
	$result = $this->db->sql_query_limit($sql, $this->config['last_total']);
    

	
	while ($row = $this->db->sql_fetchrow($result))
	{
	$this->template->assign_block_vars('last_topic', array(
            'LAST_LINK'      => 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);
As you can see, variables are fetched here and assigned to EXT array.

SO I assume it would make sense to add my own variable here and using 'topic_last_poster_id' get username_avatar from users table. Just not sure how to select it from users table.

EDIT: adding extra element to this code is not a problem. I am having issue how to select user table while still inside the array.

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Sat Oct 13, 2018 4:00 pm

You can inject user_helper class to service .yml file and constructor and just add this line to template_assign_blocks:

Code: Select all

'U_AVATAR => $this->user_loader->get_avatar($row['topic_last_poster_id']),
Or add before/after $sql

Code: Select all

$user_helper = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
to template_assign_blocks:

Code: Select all

'U_AVATAR => $user_loader->get_avatar($row['topic_last_poster_id']),
And unset($user_loader); after freeresult.
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Sun Oct 14, 2018 5:52 pm

GanstaZ wrote:
Sat Oct 13, 2018 4:00 pm
You can inject user_helper class to service .yml file and constructor and just add this line to template_assign_blocks:

Code: Select all

'U_AVATAR => $this->user_loader->get_avatar($row['topic_last_poster_id']),
Or add before/after $sql

Code: Select all

$user_helper = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
to template_assign_blocks:

Code: Select all

'U_AVATAR => $user_loader->get_avatar($row['topic_last_poster_id']),
And unset($user_loader); after freeresult.
Wow, this makes so much more sense. I was already researching maybe using JOIN tables option ;D

Tho, I am getting an error with one. No problem purging the cache, but refreshing index.php it does not load. In console simply says "Failed to load resource". SO maybe table name is wrong? Or maybe get_avatar should be something else? hmmm.

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Sun Oct 14, 2018 5:57 pm

Hmm.. If you are using example 2 (direct loading)

Code: Select all

$user_helper = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
should be

Code: Select all

$user_loader = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Sun Oct 14, 2018 6:02 pm

Oookey. Now it does load, but no resource is pulled.
The tag itself does work (tested it before with random text)

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Sun Oct 14, 2018 6:45 pm

It seems you need to load users, before we can access data:

Code: Select all

// $array of user ids

$user_loader->load_users($array);
And after we can load avatar. Or use

Code: Select all

'U_AVATAR' => $this->user_loader->get_avatar($row['topic_last_poster_id'], true, false),
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Sun Oct 14, 2018 6:52 pm

Does it matter where to place $user_loader->load_users($array); ?

EDIT: just adding it after the line where I initialize user_loader it gives same result as before: purging cache no problem, but index does not load.

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Sun Oct 14, 2018 8:30 pm

Post the code with your edits so i can see it.
"When answer lies in the question,.. question becomes redundant!"

User avatar
Kami-sama
Registered User
Posts: 104
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama » Sun Oct 14, 2018 9:30 pm

Code: Select all

//Last active topics  JOIN ' . USERS_TABLE . ' ON user_id = topic_last_poster_id
    
	$user_loader = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
    $user_loader->load_users($array);
    
    $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', 'topic') . '
		ORDER BY topic_last_post_time DESC';
	$result = $this->db->sql_query_limit($sql, $this->config['last_total']);
	
	while ($row = $this->db->sql_fetchrow($result))
	{
	$this->template->assign_block_vars('last_topic', array(
            'LAST_LINK'      => 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']),
    		'USERNAME_AV'					=> $this->user_loader->get_avatar($row['topic_last_poster_id'], true, false),
		));
	}
	$this->db->sql_freeresult($result);
    unset($user_loader);
	

User avatar
GanstaZ
Registered User
Posts: 566
Joined: Wed Oct 11, 2017 10:29 pm
Location: Zverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ » Sun Oct 14, 2018 10:30 pm

Something like this.. or use the method provided by 3Di)

Code: Select all

    
    $user_loader = new \phpbb\user_loader($this->db, $this->phpbb_root_path, $this->phpEx, USERS_TABLE);
    
    $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', 'topic') . '
		ORDER BY topic_last_post_time DESC';
	$result = $this->db->sql_query_limit($sql, $this->config['last_total']);

	$array = [];
	$user_loader->load_users($array);
	while ($row = $this->db->sql_fetchrow($result))
	{
	        $array[] = $row['topic_last_poster_id'];

	$this->template->assign_block_vars('last_topic', array(
            'LAST_LINK'      => 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']),
    		'USERNAME_AV'					=> $user_loader->get_avatar($row['topic_last_poster_id']),
		));
	}
	$this->db->sql_freeresult($result);
    unset($user_loader);
	
Last edited by GanstaZ on Mon Oct 15, 2018 12:19 am, edited 1 time in total.
"When answer lies in the question,.. question becomes redundant!"

User avatar
3Di
Registered User
Posts: 12983
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: How to fetch user avatar by user ID?

Post by 3Di » Sun Oct 14, 2018 11:21 pm

Without injection of user_loader, simply:

Code: Select all

//Last active topics
	$sql = 'SELECT t.forum_id, t.topic_id, t.topic_title, t.topic_time, t.topic_views, t.topic_poster, t.topic_posts_approved, t.topic_first_poster_name, t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_last_post_time, t.topic_last_view_time, t.topic_last_poster_id, u.user_id, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height
			FROM ' . TOPICS_TABLE . ' t, ' . USERS_TABLE . ' u 
			WHERE u.user_id = t.topic_last_poster_id
				AND ' . $this->db->sql_in_set('forum_id', $flast) . '
				AND ' . $this->content_visibility->get_visibility_sql('topic', 'topic') . '
			ORDER BY t.topic_last_post_time DESC';
	$result = $this->db->sql_query_limit($sql, $this->config['last_total']);
	
	while ($row = $this->db->sql_fetchrow($result))
	{
		/* Map arguments for  phpbb_get_avatar() */
		$row_avatar = array(
			'avatar'		 => $row['user_avatar'],
			'avatar_type'	 => $row['user_avatar_type'],
			'avatar_height'	 => 20,
			'avatar_width'	 => 20,
		);

		$topic_last_poster_av = (!empty($row['user_avatar'])) ? phpbb_get_avatar($row_avatar, '') : '';

		$this->template->assign_block_vars('last_topic', array(
			'LAST_LINK'						=> 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']),

			'TOPIC_LAST_POSTER_AV'			=> $topic_last_poster_av,
		));
	}
	$this->db->sql_freeresult($result);
in the template find:

Code: Select all

<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="" />
replace with

Code: Select all

{% if last_topic.TOPIC_LAST_POSTER_AV is not empty %}{{ last_topic.TOPIC_LAST_POSTER_AV }}{% else %}<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="" />{% endif %} 
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

Post Reply

Return to “Extension Writers Discussion”

Who is online

Users browsing this forum: No registered users and 8 guests