Adding Custom Profile Fields to Inactive Members List

tuzki · Post by **tuzki** » Sun Jul 29, 2012 9:55 pm

I have added "Full Name" as a required field upon registration, but I would like to be able to see it in the Inactive Members List for every inactive member.

I've tried adding this line to to acp_inactive.php:

Code: Select all

'FULLNAME' =>  $row['pf_fullname'],

after the line

Code: Select all

'REMINDED' => $row['user_reminded'],

And these lines to their respective places in to acp_inactive.html:

Code: Select all

<th>Full Name</th>

Code: Select all

<td style="vertical-align:top">{inactive.FULLNAME}</td>

The column for the field appears but not the field value itself. Any help would be appreciated! Thank you!

vipaka · Post by **vipaka** » Thu Aug 02, 2012 9:03 pm

I'm not sure what the "Inactive Members List" is, but if you're talking about the memberlist (memberlist.php) then you need to add something like this. If you were actually trying to mod a different file, just find the user-specific row value (in the case below, $member) and use it with your variable assignment instead.

Code: Select all

$fullname  = $member['whatever_custom_table_value_name_you_added']; //(somewhere beneath the SQL select query calling the value you are trying to retrieve)

'FULLNAME' => $fullname, //this goes in the template loop of all values associated with that user.

tuzki · Post by **tuzki** » Thu Aug 02, 2012 9:41 pm

Because my forum requires Admin validation, members that are not yet verified are viewable on the Inactive Members list in the ACP. Thank you for your suggestion though, I will try it out!

manic2 · Post by **manic2** » Sat Aug 04, 2012 12:45 pm

Your first post is on the right lines, it's just that you haven't yet queried the database for the custom profile field.

Open:-
\includes\functions_admin.php
Find

Code: Select all

	$sql = 'SELECT *
		FROM ' . USERS_TABLE . '
		WHERE user_type = ' . USER_INACTIVE .
		(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
		ORDER BY $sort_by";
	$result = $db->sql_query_limit($sql, $limit, $offset);

Replace with

Code: Select all

	$sql = 'SELECT u.* , pfd.pf_fullname
		FROM ' . USERS_TABLE . ' u
		INNER JOIN ' . PROFILE_FIELDS_DATA_TABLE . ' pfd ON pfd.user_id = u.user_id			WHERE user_type = ' . USER_INACTIVE .
		(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
		ORDER BY $sort_by";
	$result = $db->sql_query_limit($sql, $limit, $offset);

tuzki · Post by **tuzki** » Fri Aug 10, 2012 1:52 am

manic2 wrote:Your first post is on the right lines, it's just that you haven't yet queried the database for the custom profile field.

Open:-
\includes\functions_admin.php
Find

Code: Select all

	$sql = 'SELECT *
		FROM ' . USERS_TABLE . '
		WHERE user_type = ' . USER_INACTIVE .
		(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
		ORDER BY $sort_by";
	$result = $db->sql_query_limit($sql, $limit, $offset);

Replace with

Code: Select all

	$sql = 'SELECT u.* , pfd.pf_fullname
		FROM ' . USERS_TABLE . ' u
		INNER JOIN ' . PROFILE_FIELDS_DATA_TABLE . ' pfd ON pfd.user_id = u.user_id			WHERE user_type = ' . USER_INACTIVE .
		(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
		ORDER BY $sort_by";
	$result = $db->sql_query_limit($sql, $limit, $offset);

This worked - thank you SO much!

If you don't mind me asking, how would I go about adding a second custom profile field? I want to include something like "User ID" (a series of digits) next to the Full Name.

I've added

Code: Select all

'UID' =>  $row['pf_uid'],

and

Code: Select all

<td style="vertical-align:top">{inactive.UID}</td>

to their respective places, but nothing shows up.

Please let me know, thanks!

Edit:
Nevermind, I added "pfd.pf_uid" to "$sql = 'SELECT u.* , pfd.pf_fullname".

volmark · Post by **volmark** » Sat Aug 03, 2013 10:01 am

It does not work for me. I've tried with my own field name pfd.fornavn but I got SQL error message
I have also tried to call all fields at once with pfd.* but nothing happened, the fields did not appear.
Any ideas?

$sql = 'SELECT u.* , pfd.fornavn
FROM ' . USERS_TABLE . ' u
INNER JOIN ' . PROFILE_FIELDS_DATA_TABLE . ' pfd ON pfd.user_id = u.user_id
WHERE user_type = ' . USER_INACTIVE .
(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
ORDER BY $sort_by";
$result = $db->sql_query_limit($sql, $limit, $offset);

Error message:
General Error
SQL ERROR [ mysqli ]

Unknown column 'pfd.fornavn' in 'field list' [1054]

SQL

SELECT u.* , pfd.fornavn FROM phpbb3_users u INNER JOIN phpbb3_profile_fields_data pfd ON pfd.user_id = u.user_id WHERE user_type = 1 ORDER BY user_inactive_time DESC LIMIT 25

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/includes/db/dbal.php
LINE: 757
CALL: trigger_error()

FILE: [ROOT]/includes/db/mysqli.php
LINE: 182
CALL: dbal->sql_error()

FILE: [ROOT]/includes/db/mysqli.php
LINE: 224
CALL: dbal_mysqli->sql_query()

FILE: [ROOT]/includes/db/dbal.php
LINE: 170
CALL: dbal_mysqli->_sql_query_limit()

FILE: [ROOT]/includes/functions_admin.php
LINE: 2933
CALL: dbal->sql_query_limit()

FILE: [ROOT]/includes/acp/acp_inactive.php
LINE: 255
CALL: view_inactive_users()

FILE: [ROOT]/includes/functions_module.php
LINE: 507
CALL: acp_inactive->main()

volmark · Post by **volmark** » Sat Aug 03, 2013 11:09 am

I have changed pfd.fornavn to pfd.pf_fornavn. The error message does not appear any more but the custom field "fornavn" not shown in profile of inactive user.

manic2 · Post by **manic2** » Sat Aug 03, 2013 1:11 pm

volmark wrote:I have changed pfd.fornavn to pfd.pf_fornavn. The error message does not appear any more but the custom field "fornavn" not shown in profile of inactive user.

Have you done the HTML edits as well??? If you have double & triple check these.
Have you cleared your cache after the HTML edits??

volmark · Post by **volmark** » Sat Aug 03, 2013 3:00 pm

No, I did not. I don't know how to do this

Are there some examples?

manic2 · Post by **manic2** » Sat Aug 03, 2013 3:12 pm

They are in tuzki's first post above.

Follow those examples to edit acp_inactive.php & acp_inactive.html.

volmark · Post by **volmark** » Sat Aug 03, 2013 5:53 pm

There are no lines as described above in /adm/style/acp_inactive.html and /includes/acp/acp_inactive.php

acp_inactive.php

<?php
/**
*
* @package acp
* @version $Id$
* @copyright (c) 2006 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/

/**
* @ignore
*/
if (!defined('IN_PHPBB'))
{
exit;
}

/**
* @package acp
*/
class acp_inactive
{
var $u_action;
var $p_master;

function acp_inactive(&$p_master)
{
$this->p_master = &$p_master;
}

function main($id, $mode)
{
global $config, $db, $user, $auth, $template;
global $phpbb_root_path, $phpbb_admin_path, $phpEx, $table_prefix;

include($phpbb_root_path . 'includes/functions_user.' . $phpEx);

$user->add_lang('memberlist');

$action = request_var('action', '');
$mark = (isset($_REQUEST['mark'])) ? request_var('mark', array(0)) : array();
$start = request_var('start', 0);
$submit = isset($_POST['submit']);

// Sort keys
$sort_days = request_var('st', 0);
$sort_key = request_var('sk', 'i');
$sort_dir = request_var('sd', 'd');

$form_key = 'acp_inactive';
add_form_key($form_key);

// We build the sort key and per page settings here, because they may be needed later

// Number of entries to display
$per_page = request_var('users_per_page', (int) $config['topics_per_page']);

// Sorting
$limit_days = array(0 => $user->lang['ALL_ENTRIES'], 1 => $user->lang['1_DAY'], 7 => $user->lang['7_DAYS'], 14 => $user->lang['2_WEEKS'], 30 => $user->lang['1_MONTH'], 90 => $user->lang['3_MONTHS'], 180 => $user->lang['6_MONTHS'], 365 => $user->lang['1_YEAR']);
$sort_by_text = array('i' => $user->lang['SORT_INACTIVE'], 'j' => $user->lang['SORT_REG_DATE'], 'l' => $user->lang['SORT_LAST_VISIT'], 'd' => $user->lang['SORT_LAST_REMINDER'], 'r' => $user->lang['SORT_REASON'], 'u' => $user->lang['SORT_USERNAME'], 'p' => $user->lang['SORT_POSTS'], 'e' => $user->lang['SORT_REMINDER']);
$sort_by_sql = array('i' => 'user_inactive_time', 'j' => 'user_regdate', 'l' => 'user_lastvisit', 'd' => 'user_reminded_time', 'r' => 'user_inactive_reason', 'u' => 'username_clean', 'p' => 'user_posts', 'e' => 'user_reminded');

$s_limit_days = $s_sort_key = $s_sort_dir = $u_sort_param = '';
gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sort_dir, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);

if ($submit && sizeof($mark))
{
if ($action !== 'delete' && !check_form_key($form_key))
{
trigger_error($user->lang['FORM_INVALID'] . adm_back_link($this->u_action), E_USER_WARNING);
}

switch ($action)
{
case 'activate':
case 'delete':

$sql = 'SELECT user_id, username
FROM ' . USERS_TABLE . '
WHERE ' . $db->sql_in_set('user_id', $mark);
$result = $db->sql_query($sql);

$user_affected = array();
while ($row = $db->sql_fetchrow($result))
{
$user_affected[$row['user_id']] = $row['username'];
}
$db->sql_freeresult($result);

if ($action == 'activate')
{
// Get those 'being activated'...
$sql = 'SELECT user_id, username' . (($config['require_activation'] == USER_ACTIVATION_ADMIN) ? ', user_email, user_lang' : '') . '
FROM ' . USERS_TABLE . '
WHERE ' . $db->sql_in_set('user_id', $mark) . '
AND user_type = ' . USER_INACTIVE;
$result = $db->sql_query($sql);

$inactive_users = array();
while ($row = $db->sql_fetchrow($result))
{
$inactive_users[] = $row;
}
$db->sql_freeresult($result);

user_active_flip('activate', $mark);

if ($config['require_activation'] == USER_ACTIVATION_ADMIN && !empty($inactive_users))
{
include_once($phpbb_root_path . 'includes/functions_messenger.' . $phpEx);

$messenger = new messenger(false);

foreach ($inactive_users as $row)
{
$messenger->template('admin_welcome_activated', $row['user_lang']);

$messenger->to($row['user_email'], $row['username']);

$messenger->anti_abuse_headers($config, $user);

$messenger->assign_vars(array(
'USERNAME' => htmlspecialchars_decode($row['username']))
);

$messenger->send(NOTIFY_EMAIL);
}

$messenger->save_queue();
}

if (!empty($inactive_users))
{
foreach ($inactive_users as $row)
{
add_log('admin', 'LOG_USER_ACTIVE', $row['username']);
add_log('user', $row['user_id'], 'LOG_USER_ACTIVE_USER');
}
}

// For activate we really need to redirect, else a refresh can result in users being deactivated again
$u_action = $this->u_action . "&$u_sort_param&start=$start";
$u_action .= ($per_page != $config['topics_per_page']) ? "&users_per_page=$per_page" : '';

redirect($u_action);
}
else if ($action == 'delete')
{
if (confirm_box(true))
{
if (!$auth->acl_get('a_userdel'))
{
trigger_error($user->lang['NO_AUTH_OPERATION'] . adm_back_link($this->u_action), E_USER_WARNING);
}

foreach ($mark as $user_id)
{
user_delete('retain', $user_id, $user_affected[$user_id]);
}

add_log('admin', 'LOG_INACTIVE_' . strtoupper($action), implode(', ', $user_affected));
}
else
{
$s_hidden_fields = array(
'mode' => $mode,
'action' => $action,
'mark' => $mark,
'submit' => 1,
'start' => $start,
);
confirm_box(false, $user->lang['CONFIRM_OPERATION'], build_hidden_fields($s_hidden_fields));
}
}

break;

case 'remind':
if (empty($config['email_enable']))
{
trigger_error($user->lang['EMAIL_DISABLED'] . adm_back_link($this->u_action), E_USER_WARNING);
}

$sql = 'SELECT user_id, username, user_email, user_lang, user_jabber, user_notify_type, user_regdate, user_actkey
FROM ' . USERS_TABLE . '
WHERE ' . $db->sql_in_set('user_id', $mark) . '
AND user_inactive_reason';

$sql .= ($config['require_activation'] == USER_ACTIVATION_ADMIN) ? ' = ' . INACTIVE_REMIND : ' <> ' . INACTIVE_MANUAL;

$result = $db->sql_query($sql);

if ($row = $db->sql_fetchrow($result))
{
// Send the messages
include_once($phpbb_root_path . 'includes/functions_messenger.' . $phpEx);

$messenger = new messenger();
$usernames = $user_ids = array();

do
{
$messenger->template('user_remind_inactive', $row['user_lang']);

$messenger->to($row['user_email'], $row['username']);
$messenger->im($row['user_jabber'], $row['username']);

$messenger->anti_abuse_headers($config, $user);

$messenger->assign_vars(array(
'USERNAME' => htmlspecialchars_decode($row['username']),
'REGISTER_DATE' => $user->format_date($row['user_regdate'], false, true),
'U_ACTIVATE' => generate_board_url() . "/ucp.$phpEx?mode=activate&u=" . $row['user_id'] . '&k=' . $row['user_actkey'])
);

$messenger->send($row['user_notify_type']);

$usernames[] = $row['username'];
$user_ids[] = (int) $row['user_id'];
}
while ($row = $db->sql_fetchrow($result));

$messenger->save_queue();

// Add the remind state to the database
$sql = 'UPDATE ' . USERS_TABLE . '
SET user_reminded = user_reminded + 1,
user_reminded_time = ' . time() . '
WHERE ' . $db->sql_in_set('user_id', $user_ids);
$db->sql_query($sql);

add_log('admin', 'LOG_INACTIVE_REMIND', implode(', ', $usernames));
unset($usernames);
}
$db->sql_freeresult($result);

// For remind we really need to redirect, else a refresh can result in more than one reminder
$u_action = $this->u_action . "&$u_sort_param&start=$start";
$u_action .= ($per_page != $config['topics_per_page']) ? "&users_per_page=$per_page" : '';

redirect($u_action);

break;
}
}

// Define where and sort sql for use in displaying logs
$sql_where = ($sort_days) ? (time() - ($sort_days * 86400)) : 0;
$sql_sort = $sort_by_sql[$sort_key] . ' ' . (($sort_dir == 'd') ? 'DESC' : 'ASC');

$inactive = array();
$inactive_count = 0;

$start = view_inactive_users($inactive, $inactive_count, $per_page, $start, $sql_where, $sql_sort);

foreach ($inactive as $row)
{
$template->assign_block_vars('inactive', array(
'INACTIVE_DATE' => $user->format_date($row['user_inactive_time']),
'REMINDED_DATE' => $user->format_date($row['user_reminded_time']),
'JOINED' => $user->format_date($row['user_regdate']),
'LAST_VISIT' => (!$row['user_lastvisit']) ? ' - ' : $user->format_date($row['user_lastvisit']),

'REASON' => $row['inactive_reason'],
'USER_ID' => $row['user_id'],
'POSTS' => ($row['user_posts']) ? $row['user_posts'] : 0,
'REMINDED' => $row['user_reminded'],

'REMINDED_EXPLAIN' => $user->lang('USER_LAST_REMINDED', (int) $row['user_reminded'], $user->format_date($row['user_reminded_time'])),

'USERNAME_FULL' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'], false, append_sid("{$phpbb_admin_path}index.$phpEx", 'i=users&mode=overview')),
'USERNAME' => get_username_string('username', $row['user_id'], $row['username'], $row['user_colour']),
'USER_COLOR' => get_username_string('colour', $row['user_id'], $row['username'], $row['user_colour']),

'U_USER_ADMIN' => append_sid("{$phpbb_admin_path}index.$phpEx", "i=users&mode=overview&u={$row['user_id']}"),
'U_SEARCH_USER' => ($auth->acl_get('u_search')) ? append_sid("{$phpbb_root_path}search.$phpEx", "author_id={$row['user_id']}&sr=posts") : '',
));
}

$option_ary = array('activate' => 'ACTIVATE', 'delete' => 'DELETE');
if ($config['email_enable'])
{
$option_ary += array('remind' => 'REMIND');
}

$template->assign_vars(array(
'S_INACTIVE_USERS' => true,
'S_INACTIVE_OPTIONS' => build_select($option_ary),

'S_LIMIT_DAYS' => $s_limit_days,
'S_SORT_KEY' => $s_sort_key,
'S_SORT_DIR' => $s_sort_dir,
'S_ON_PAGE' => on_page($inactive_count, $per_page, $start),
'PAGINATION' => generate_pagination($this->u_action . "&$u_sort_param&users_per_page=$per_page", $inactive_count, $per_page, $start, true),
'USERS_PER_PAGE' => $per_page,

'U_ACTION' => $this->u_action . "&$u_sort_param&users_per_page=$per_page&start=$start",
));

$this->tpl_name = 'acp_inactive';
$this->page_title = 'ACP_INACTIVE_USERS';
}
}

?>

==========================================================================

acp_inactive.html



<a name="maincontent"></a>

<h2>{L_INACTIVE_USERS}</h2>

<p>{L_INACTIVE_USERS_EXPLAIN}</p>

<form id="inactive" method="post" action="{U_ACTION}">

<div class="clearfix"></div>


<div class="pagination">
<a href="#" onclick="jumpto(); return false;" title="{L_JUMP_TO_PAGE}">{S_ON_PAGE}</a> • <span>{PAGINATION}</span>
</div>


<table cellspacing="1">
<thead>
<tr>
<th>{L_USERNAME}</th>
<th>{L_JOINED}</th>
<th>{L_INACTIVE_DATE}</th>
<th>{L_LAST_VISIT}</th>
<th>{L_INACTIVE_REASON}</th>
<th>{L_MARK}</th>
</tr>
</thead>
<tbody>

<tr class="row1"><tr class="row2">

<td style="vertical-align: top;">
{inactive.USERNAME_FULL}
<br />{L_POSTS}: <strong>{inactive.POSTS}</strong> [<a href="{inactive.U_SEARCH_USER}">{L_SEARCH_USER_POSTS}</a>]
</td>
<td style="vertical-align: top;">{inactive.JOINED}</td>
<td style="vertical-align: top;">{inactive.INACTIVE_DATE}</td>
<td style="vertical-align: top;">{inactive.LAST_VISIT}</td>
<td style="vertical-align: top;">
{inactive.REASON}
<br />{inactive.REMINDED_EXPLAIN}
</td>
<td> <input type="checkbox" class="radio" name="mark[]" value="{inactive.USER_ID}" /> </td>
</tr>

<tr>
<td colspan="6" style="text-align: center;">{L_NO_INACTIVE_USERS}</td>
</tr>

</tbody>
</table>

<fieldset class="display-options">
{L_DISPLAY_LOG}:  {S_LIMIT_DAYS} {L_SORT_BY}: {S_SORT_KEY} {S_SORT_DIR} Users per page: <input class="inputbox autowidth" type="text" name="users_per_page" id="users_per_page" size="3" value="{USERS_PER_PAGE}" />
<input class="button2" type="submit" value="{L_GO}" name="sort" />
</fieldset>

<hr />


<div class="pagination">
<a href="#" onclick="jumpto(); return false;" title="{L_JUMP_TO_PAGE}">{S_ON_PAGE}</a> • <span>{PAGINATION}</span>
</div>


<fieldset class="quick">
<select name="action">{S_INACTIVE_OPTIONS}</select>
<input class="button2" type="submit" name="submit" value="{L_SUBMIT}" />
<p class="small"><a href="#" onclick="marklist('inactive', 'mark', true); return false;">{L_MARK_ALL}</a> • <a href="#" onclick="marklist('inactive', 'mark', false); return false;">{L_UNMARK_ALL}</a></p>
{S_FORM_TOKEN}
</fieldset>

</form>

volmark · Post by **volmark** » Sat Aug 03, 2013 5:58 pm

Is there possible to retrieve all customs fields at once with the wildcards sign like *

manic2 · Post by **manic2** » Sat Aug 03, 2013 6:22 pm

Yes there are:-

volmark wrote:
'REASON' => $row['inactive_reason'],
'USER_ID' => $row['user_id'],
'POSTS' => ($row['user_posts']) ? $row['user_posts'] : 0,
'REMINDED' => $row['user_reminded'],

manic2 · Post by **manic2** » Sat Aug 03, 2013 6:24 pm

volmark · Post by **volmark** » Sat Aug 03, 2013 6:54 pm

OK, but what about acp_inactive.html. How I can find the right place?
Generally say it is a bad idea to fetch one field at a time. We have 3 administrators in organisation and if one of us change custom field through ACP without changing corresponding lines in acp_inactive.html and acp_inactive.php the system will be crashed.
Is here the way to retrieve all customs fields for inactive users at once?