Getting Username from DB

Discussion forum for MOD Writers regarding MOD Development.
User avatar
kinerity
Community Team Member
Community Team Member
Posts: 2307
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: Getting Username from DB

Post by kinerity » Tue Apr 21, 2015 3:51 am

FoulFoot's code will assign $username to an array (since it's using $db->sql_fetchrow). Try the following:

Code: Select all

$sql = 'SELECT username
    FROM ' . USERS_TABLE . '
    WHERE user_id = ' . (int) $user_id;
$result = $db->sql_query($sql);
$username = $db->sql_fetchfield('username');
$db->sql_freeresult($result); 
Kailey Truscott - Community Team

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Wed Apr 22, 2015 6:52 pm

FoulFoot wrote:Put this after the code I provided, and report back what it prints out:

Code: Select all

var_dump($username);
die;
Foul
A blank page shows, with this on it:

array(1) { ["username"]=> string(5) "admin" }

admin = username

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Wed Apr 22, 2015 8:02 pm

kinerity wrote:FoulFoot's code will assign $username to an array (since it's using $db->sql_fetchrow). Try the following:

Code: Select all

$sql = 'SELECT username
    FROM ' . USERS_TABLE . '
    WHERE user_id = ' . (int) $user_id;
$result = $db->sql_query($sql);
$username = $db->sql_fetchfield('username');
$db->sql_freeresult($result); 
Thanks, that works with grabbing the username.
But here is an issue: only the first comment is showing of each page. I assume it has something to do with me placing your code right below // User rank . Maybe the 2 sql things have to be combined? Here is the entire function I'm working with:

Code: Select all

function show($user_id)
	{
		global $db, $config, $user, $phpbb_root_path, $auth, $comment, $comment_id, $phpEx, $template;
		
		$start   = request_var('start', 0);
		$limit  = request_var('limit', (int) $config['profile_comments_limit']);

		$pagination_url = append_sid("memberlist.php?mode=viewprofile&u=" . $user_id . "#wall");
		
		$sql_array = array(
			'SELECT'    => 'c.*, u.user_id, u.username, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, u.user_colour, u.user_rank',
			'FROM'      => array(
				PROFILE_COMMENTS_TABLE => 'c',
				USERS_TABLE 		   => 'u'
			),
			'WHERE'     =>  'c.poster_id = u.user_id
						AND c.profile_id = ' . $user_id,

			'ORDER_BY'  => 'c.comment_time '. $config['profile_comments_order'],
		);
		$sql = $db->sql_build_query('SELECT', $sql_array);
		$result = $db->sql_query_limit($sql, $limit, $start);

		while($row = $db->sql_fetchrow($result))
		{
		
			// Generate text for display
			$row['bbcode_options'] = (($row['enable_bbcode']) ? OPTION_FLAG_BBCODE : 0) +
				(($row['enable_smilies']) ? OPTION_FLAG_SMILIES : 0) + 
				(($row['enable_magic_url']) ? OPTION_FLAG_LINKS : 0);
			$text = generate_text_for_display($row['text'], $row['bbcode_uid'], $row['bbcode_bitfield'], $row['bbcode_options']);
		
			// User rank
			$sql = 'SELECT rank_title
				FROM ' . RANKS_TABLE . '
				WHERE rank_id = ' . $row['user_rank'] . '';
			$sqlresult = $db->sql_query($sql);
			$rank = $db->sql_fetchrow($sqlresult);
			$db->sql_freeresult($sqlresult);
			
			// Comment edit and delete permissions
			$comdelete_allowed = ($user->data['is_registered'] && ($auth->acl_get('m_delete_profile_comments') || (
				$user->data['user_id'] == $row['poster_id'] &&
				$auth->acl_get('u_delete_own_profile_comments')
			)));
			$comedit_allowed = ($user->data['is_registered'] && ($auth->acl_get('m_edit_profile_comments') || (
				$user->data['user_id'] == $row['poster_id'] &&
				$auth->acl_get('u_edit_own_profile_comments')
			)));
			
				if ($comment == 'edit' && $comment_id && !$comedit_allowed)
				{
					$this->error($user->lang('NOT_ALLOWED_EDIT_COMMENT'),'BACK_PROFILE');
				}
				
				if ($comment == 'delete' && $comment_id && !$comdelete_allowed)
				{
					$this->error($user->lang('NOT_ALLOWED_DELETE_COMMENT'),'BACK_PROFILE');
				}
			
				$template->assign_block_vars('comment', array(
					'TEXT'		    => $text,
					'COMMENT_DATE'			=> $user->format_date($row['comment_time'], false),
					'AVATAR'				=> get_user_avatar($row['user_avatar'], $row['user_avatar_type'], $row['user_avatar_width'] / 2, $row['user_avatar_height'] / 2),
					'USERNAME'				=> $row['username'],
					'COLOUR'				=> $row['user_colour'],
					'COMMENT_ID'			=> $row['comment_id'],
					'RANK_TITLE'			=> $rank['rank_title'],
					'S_COMEDIT_ALLOWED'		=> $comedit_allowed,
					'S_COMDELETE_ALLOWED'	=> $comdelete_allowed,
					'U_COMMENT_EDIT'		=> append_sid('memberlist.php?mode=viewprofile&comment=edit&cid='.$row['comment_id'].'' . (($start) ? "&start=$start" : '') .'&u=' . $user_id . '#message-box'),
					'U_COMMENT_DELETE'		=> append_sid('memberlist.php?mode=viewprofile&comment=delete&cid='.$row['comment_id'].'&u=' . $user_id),
					'U_PROFILE'				=>  append_sid("memberlist.php?mode=viewprofile&u=" .$row['poster_id']. ""),
				));
		}
		$db->sql_freeresult($result);
	

		$sql_array['SELECT'] = 'COUNT(c.comment_id) as total_comments';
		$sql = $db->sql_build_query('SELECT', $sql_array);
		$countresult = $db->sql_query($sql);

		$total_comments = $db->sql_fetchfield('total_comments');

		$db->sql_freeresult($countresult);
		
		$template->assign_vars(array(
			'PAGINATION'        => generate_pagination($pagination_url, $total_comments, $config['profile_comments_limit'], $start),
			'PAGE_NUMBER'       => on_page($total_comments, $limit, $start),
			'TOTAL_COMMENTS'    => ($total_comments == 1) ? $user->lang['COMMENT_COUNT'] : sprintf($user->lang['COMMENTS_COUNT'], $total_comments),
			'TOTAL_COMMENTS_NUMERIC' => $total_comments,
		));
		
		return true;

	}
EDIT: I deleted this:

Code: Select all

// User rank
			$sql = 'SELECT rank_title
				FROM ' . RANKS_TABLE . '
				WHERE rank_id = ' . $row['user_rank'] . '';
			$sqlresult = $db->sql_query($sql);
			$rank = $db->sql_fetchrow($sqlresult);
			$db->sql_freeresult($sqlresult);
And all of the comments still loaded. When I placed your code, it loaded only the first one of each comments page. So I don't know if that helps with solving the issue. Thanks

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Wed Apr 29, 2015 8:46 pm

bump..

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21034
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: Getting Username from DB

Post by RMcGirr83 » Thu Apr 30, 2015 10:30 am

Try replacing that query with this

$user_rank_data = phpbb_get_user_rank($row['user_rank'], (($row['user_id'] == ANONYMOUS) ? false : $row['user_posts']));

you will have to add in u.user_posts in your initial sql. The above will return rank title and rank image.
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Tue May 05, 2015 6:22 pm

RMcGirr83 wrote:Try replacing that query with this

$user_rank_data = phpbb_get_user_rank($row['user_rank'], (($row['user_id'] == ANONYMOUS) ? false : $row['user_posts']));

you will have to add in u.user_posts in your initial sql. The above will return rank title and rank image.
I get this:

Code: Select all

Fatal error: Call to undefined function phpbb_get_user_rank() 
I am able to grab the username with kinerity's code, except the profile comments just keep loading the first comment on each page, even if I delete this:

Code: Select all

// User rank
         $sql = 'SELECT rank_title
            FROM ' . RANKS_TABLE . '
            WHERE rank_id = ' . $row['user_rank'] . '';
         $sqlresult = $db->sql_query($sql);
         $rank = $db->sql_fetchrow($sqlresult);
         $db->sql_freeresult($sqlresult);

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21034
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: Getting Username from DB

Post by RMcGirr83 » Wed May 06, 2015 9:42 am

TheTechGame wrote:
RMcGirr83 wrote:Try replacing that query with this

$user_rank_data = phpbb_get_user_rank($row['user_rank'], (($row['user_id'] == ANONYMOUS) ? false : $row['user_posts']));

you will have to add in u.user_posts in your initial sql. The above will return rank title and rank image.
I get this:

Code: Select all

Fatal error: Call to undefined function phpbb_get_user_rank() 
You need to include the includes/functions_display.php file to get the function

Code: Select all

    function show($user_id)
    {
        global $db, $config, $user, $phpbb_root_path, $auth, $comment, $comment_id, $phpEx, $template;
        if (!function_exists('phpbb_get_user_rank'))
        {
            include($phpbb_root_path . 'includes/functions_display.' . $phpEx);
        } 
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Wed Jun 24, 2015 4:51 pm

RMcGirr83 wrote:You need to include the includes/functions_display.php file to get the function
I get this error when using that:
Fatal error: Cannot redeclare display_forums() (previously declared in /includes/functions_display.php:22) in /includes/functions_display.php on line 568

Here is that entire function as it is now, with your suggested edit.

Code: Select all

/**
	* Show comments
	*@param int $user_id id of user
	*/
	function show($user_id)
    {
        global $db, $config, $user, $phpbb_root_path, $auth, $comment, $comment_id, $phpEx, $template;
        if (!function_exists('phpbb_get_user_rank'))
        {
            include($phpbb_root_path . 'includes/functions_display.' . $phpEx);
        }
		
		$start   = request_var('start', 0);
		$limit  = request_var('limit', (int) $config['profile_comments_limit']);

		$pagination_url = append_sid("$username" . $user_id . "#wall");
		
		$sql_array = array(
			'SELECT'    => 'c.*, u.user_id, u.username, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, u.user_colour, u.user_rank',
			'FROM'      => array(
				PROFILE_COMMENTS_TABLE => 'c',
				USERS_TABLE 		   => 'u'
			),
			'WHERE'     =>  'c.poster_id = u.user_id
						AND c.profile_id = ' . $user_id,

			'ORDER_BY'  => 'c.comment_time '. $config['profile_comments_order'],
		);
		$sql = $db->sql_build_query('SELECT', $sql_array);
		$result = $db->sql_query_limit($sql, $limit, $start);

		while($row = $db->sql_fetchrow($result))
		{
		
			// Generate text for display
			$row['bbcode_options'] = (($row['enable_bbcode']) ? OPTION_FLAG_BBCODE : 0) +
				(($row['enable_smilies']) ? OPTION_FLAG_SMILIES : 0) + 
				(($row['enable_magic_url']) ? OPTION_FLAG_LINKS : 0);
			$text = generate_text_for_display($row['text'], $row['bbcode_uid'], $row['bbcode_bitfield'], $row['bbcode_options']);
		
// User rank replaced with this, to grab username. Leaving this, and not using the User Rank code below makes only the first comment of each page show.
			$sql = 'SELECT username
    			FROM ' . USERS_TABLE . '
    			WHERE user_id = ' . (int) $user_id;
			$result = $db->sql_query($sql);
			$username = $db->sql_fetchfield('username');
			$db->sql_freeresult($result);

// User Rank
//			$sql = 'SELECT rank_title
//				FROM ' . RANKS_TABLE . '
//				WHERE rank_id = ' . $row['user_rank'] . '';
//			$sqlresult = $db->sql_query($sql);
//			$rank = $db->sql_fetchrow($sqlresult);
//			$db->sql_freeresult($sqlresult);
			
			
			// Comment edit and delete permissions
			$comdelete_allowed = ($user->data['is_registered'] && ($auth->acl_get('m_delete_profile_comments') || (
				$user->data['user_id'] == $row['poster_id'] &&
				$auth->acl_get('u_delete_own_profile_comments')
			)));
			$comedit_allowed = ($user->data['is_registered'] && ($auth->acl_get('m_edit_profile_comments') || (
				$user->data['user_id'] == $row['poster_id'] &&
				$auth->acl_get('u_edit_own_profile_comments')
			)));
			
				if ($comment == 'edit' && $comment_id && !$comedit_allowed)
				{
					$this->error($user->lang('NOT_ALLOWED_EDIT_COMMENT'),'BACK_PROFILE');
				}
				
				if ($comment == 'delete' && $comment_id && !$comdelete_allowed)
				{
					$this->error($user->lang('NOT_ALLOWED_DELETE_COMMENT'),'BACK_PROFILE');
				}
			
				$template->assign_block_vars('comment', array(
					'TEXT'		    => $text,
					'COMMENT_DATE'			=> $user->format_date($row['comment_time'], false),
					'AVATAR'				=> get_user_avatar($row['user_avatar'], $row['user_avatar_type'], $row['user_avatar_width'] / 2, $row['user_avatar_height'] / 2),
					'USERNAME'				=> $row['username'],
					'COLOUR'				=> $row['user_colour'],
					'COMMENT_ID'			=> $row['comment_id'],
					'RANK_TITLE'			=> $rank['rank_title'],
					'S_COMEDIT_ALLOWED'		=> $comedit_allowed,
					'S_COMDELETE_ALLOWED'	=> $comdelete_allowed,
					'U_COMMENT_EDIT'		=> append_sid($username.'&comment=edit&cid='.$row['comment_id'].'' . (($start) ? "&start=$start" : '') .'&u=' . $user_id . '#message-box'),
					'U_COMMENT_DELETE'		=> append_sid($row['username'].'&comment=delete&cid='.$row['comment_id']),
					'U_PROFILE'				=>  append_sid($row['username']),
				));
		}
		$db->sql_freeresult($result);
	

		$sql_array['SELECT'] = 'COUNT(c.comment_id) as total_comments';
		$sql = $db->sql_build_query('SELECT', $sql_array);
		$countresult = $db->sql_query($sql);

		$total_comments = $db->sql_fetchfield('total_comments');

		$db->sql_freeresult($countresult);
		
		$template->assign_vars(array(
			'PAGINATION'        => generate_pagination($pagination_url, $total_comments, $config['profile_comments_limit'], $start),
			'PAGE_NUMBER'       => on_page($total_comments, $limit, $start),
			'TOTAL_COMMENTS'    => ($total_comments == 1) ? $user->lang['COMMENT_COUNT'] : sprintf($user->lang['COMMENTS_COUNT'], $total_comments),
			'TOTAL_COMMENTS_NUMERIC' => $total_comments,
		));
		
		return true;

	}
I cannot include functions_display more than once, although I see that function_display is not included even once anywhere in the functions_profile_comments file, except for the one you wanted me to add.

FoulFoot
Registered User
Posts: 261
Joined: Mon Jun 23, 2003 1:04 am
Location: Honolulu, HI
Contact:

Re: Getting Username from DB

Post by FoulFoot » Thu Jun 25, 2015 3:12 pm

Replace

Code: Select all

        if (!function_exists('phpbb_get_user_rank'))
        {
            include($phpbb_root_path . 'includes/functions_display.' . $phpEx);
        }
with

Code: Select all

         include_once($phpbb_root_path . 'includes/functions_display.' . $phpEx);
functions_display is somehow being called twice -- most likely from some other code that does not have a check.

Foul

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21034
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: Getting Username from DB

Post by RMcGirr83 » Fri Jun 26, 2015 10:19 am

If it was already included then the check that is being used would not include the file again.
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

FoulFoot
Registered User
Posts: 261
Joined: Mon Jun 23, 2003 1:04 am
Location: Honolulu, HI
Contact:

Re: Getting Username from DB

Post by FoulFoot » Fri Jun 26, 2015 12:01 pm

True. The error is saying that display_forum is being called on line 568, after being called on line 22. My line 568 does not have the display_forum function. Check there first to see if you've somehow copied some code.

Foul

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Tue Jun 30, 2015 6:53 am

FoulFoot wrote:True. The error is saying that display_forum is being called on line 568, after being called on line 22. My line 568 does not have the display_forum function. Check there first to see if you've somehow copied some code.

Foul
Thanks for your help, Foul and RMcGirr. I don't have access to a computer right now (on a mobile device), but I used my FTP app and I see that line 22 of my functions_display.php contains some sort of phpBB-SEO function. Might be a conflict with that. Forgot I even had that installed. I'll have a better look at it tomorrow and will reply to this thread. Thanks again

TheTechGame
Registered User
Posts: 45
Joined: Wed Oct 31, 2012 3:08 am

Re: Getting Username from DB

Post by TheTechGame » Tue Jun 30, 2015 5:08 pm

RMcGirr83 wrote:If it was already included then the check that is being used would not include the file again.
Yeah, I used include_once($phpbb_root_path . 'includes/functions_display.' . $phpEx); and it would not include the file again. I checked line 568 of functions_display.php , it is just a bracket
Lines 562-568:

Code: Select all

	if ($return_moderators)
	{
		return array($active_forum_ary, $forum_moderators);
	}

	return array($active_forum_ary, array());
}
Here is line 22 of functions_display:

Code: Select all

function display_forums($root_data = '', $display_moderators = true, $return_moderators = false)
Alright, to recap everything:

1. Using this

Code: Select all

$sql = 'SELECT username
    			FROM ' . USERS_TABLE . '
    			WHERE user_id = ' . (int) $user_id;
			$result = $db->sql_query($sql);
			$username = $db->sql_fetchfield('username');
			$db->sql_freeresult($result);
Instead of what is was before (this:)

Code: Select all

			$sql = 'SELECT rank_title
				FROM ' . RANKS_TABLE . '
				WHERE rank_id = ' . $row['user_rank'] . '';
			$sqlresult = $db->sql_query($sql);
			$rank = $db->sql_fetchrow($sqlresult);
			$db->sql_freeresult($sqlresult);
Causes only the FIRST profile comment of each page to be loaded on each pagination page.
Image
Reverting it back to what it was before the suggested edit, it loads them properly:
Image

2. I need to use the $username variable because it currently doesn't load properly, it uses the user ID (submitting redirects to site.com/user_id instead of /username).

3. This is what happens after submitting a comment: meta_refresh(3, append_sid('memberlist.php?mode=viewprofile&u='.$user_id.'')); for some reason, the ampersand appears as & in the URL and does not load right (because it has to be "&"). I tried replacing & with & and it still did not work. I need to use the & sign elsewhere, too, site.com/myusername&start=10 is what I want, it originally uses a question mark instead of &, because without the /username style URLs, it would be ok to use ?start=10 , but anyway, I need to use & signs and it won't let me without it showing the HTML code for & in the URL.

Tl:dr -
- Only 1 comment is showing on each page using the edits suggested
- & is showing as "&" in the URL instead of &. I tried using a PHP replace function to add in & and it still didn't work.

Locked

Return to “[3.0.x] MOD Writers Discussion”