How to fetch user avatar by user ID?

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

How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

user_id can be linked to last_ poster_id and fetch what you want from users table.
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

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?
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

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.
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

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);
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

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),
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

Post the code with your edits so i can see it.
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
Kami-sama
Registered User
Posts: 137
Joined: Sat May 26, 2018 3:07 pm

Re: How to fetch user avatar by user ID?

Post by Kami-sama »

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: 1187
Joined: Wed Oct 11, 2017 10:29 pm
Location: GZOverse

Re: How to fetch user avatar by user ID?

Post by GanstaZ »

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.
Usus est magister optimus! phpBB pre-Triton & latest php environment.
When answer lies in the question, question becomes redundant!
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: How to fetch user avatar by user ID?

Post by 3Di »

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 %} 
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Post Reply

Return to “Extension Writers Discussion”