Adding custom profile field to memberlist

[codejunkie]

I just happened to stumple on this post. I have read all the way through it but am still having trouble.

I am using the prosilver theme.
I have a custom profile field that appears as "Real Name" to the user and has a field id of "real_name."
I want this field to appear on the memberlist, but I don't want it to have it's own column. I want to add it to the WEBSITE, LOCATION column. Can anyone tell me how I would go about doing this?

If you have completed all of the code mentioned previously, you will just need to tweak your memberlist_body.html (Not certain on the template name at this moment) file to place your information where you want it.

[cpenticuff]

As I said before... I want to add this extra field in the memberlist inside another currently existing column, but until I am sure I understand the coding... I am just trying to create a new column for the information. I do not need this to be sortable. I have tried using the information found on the first page of this thread but I have had no luck. My Memberlist has not changed at all... it still displays exactly as it did before I began messing with the code.

The ID for my custom profile field is real_name
Here are the two sections of code I have altered in my memberlist_body.html file

Code: Select all

<th class="name"><span class="rank-img"><a href="{U_SORT_RANK}">{L_RANK}</a></span><a href="{U_SORT_USERNAME}"><!-- IF S_SHOW_GROUP -->{L_GROUP_LEADER}<!-- ELSE -->{L_USERNAME}<!-- ENDIF --></a></th>
			<th class="posts"><a href="{U_SORT_POSTS}#memberlist">{L_POSTS}</a></th>
			<th>real_name</th>
			<th class="info"><a href="{U_SORT_WEBSITE}#memberlist">{L_WEBSITE}</a>{L_COMMA_SEPARATOR}<a href="{U_SORT_LOCATION}">{L_LOCATION}</a></th>
			<th class="joined"><a href="{U_SORT_JOINED}#memberlist">{L_JOINED}</a></th>
			<!-- IF U_SORT_ACTIVE --><th class="active"><a href="{U_SORT_ACTIVE}#memberlist">{L_LAST_ACTIVE}</a></th><!-- ENDIF -->
		</tr>
		</thead>
		<tbody>
		<!-- BEGIN memberrow -->
			<!-- IF S_SHOW_GROUP -->
				<!-- IF not memberrow.S_GROUP_LEADER and not $S_MEMBER_HEADER -->
				<!-- IF memberrow.S_FIRST_ROW -->
				<tr class="bg1">
					<td colspan="<!-- IF U_SORT_ACTIVE -->5<!-- ELSE -->4<!-- ENDIF -->">&nbsp;</td>
				</tr>
				

Code: Select all

<tr class="<!-- IF memberrow.S_ROW_COUNT is even -->bg1<!-- ELSE -->bg2<!-- ENDIF -->">
		<td><!-- IF memberrow.RANK_IMG --><span class="rank-img">{memberrow.RANK_IMG}</span><!-- ELSE --><span class="rank-img">{memberrow.RANK_TITLE}</span><!-- ENDIF --><!-- IF S_IN_SEARCH_POPUP and not S_SELECT_SINGLE --><input type="checkbox" name="user" value="{memberrow.USERNAME}" /> <!-- ENDIF -->{memberrow.USERNAME_FULL}<!-- IF S_SELECT_SINGLE --><br />[&nbsp;<a href="#" onclick="insert_single('{memberrow.A_USERNAME}'); return false;">{L_SELECT}</a>&nbsp;]<!-- ENDIF --></td>
		<td class="posts"><!-- IF memberrow.POSTS --><a href="{memberrow.U_SEARCH_USER}" title="{L_SEARCH_USER_POSTS}">{memberrow.POSTS}</a><!-- ELSE -->{memberrow.POSTS}<!-- ENDIF --></td>
		<td><!-- IF memberrow.PROFILE_real_name_VALUE --> {memberrow.PROFILE_real_name_VALUE} <!--ELSE -->&nbsp<!-- ENDIF --></td>
		<td class="info"><!-- IF memberrow.U_WWW or memberrow.LOCATION --><!-- IF memberrow.U_WWW --><div><a href="{memberrow.U_WWW}" title="{L_VISIT_WEBSITE}: {memberrow.U_WWW}">{memberrow.U_WWW}</a></div><!-- ENDIF --><!-- IF memberrow.LOCATION --><div>{memberrow.LOCATION}</div><!-- ENDIF --><!-- ELSE -->&nbsp;<!-- ENDIF --></td>
		<td>{memberrow.JOINED}</td>
		<!-- IF S_VIEWONLINE --><td>{memberrow.VISITED}&nbsp;</td><!-- ENDIF -->
	</tr>

I'm not really sure why nothing has changed at all. Does anyone see anything wrong?

Edit: I now have the column displaying but it is empty... I had forgot to empty my cache.

[codejunkie]

I think this memberrow.PROFILE_real_name_VALUE should be changed to this memberrow.PROFILE_REAL_NAME_VALUE. That is assuming the custom field is real_name (irregardless of capitalization). And remember to clear your cache afterward.

[redryder]

Ok, I have recoded as described here and have one of my custom fields sortable, but the post only describes how to do this on one custom field. I have two that I need to sort. The only part of the code I can't seem to customize is this:

Code: Select all

if(isset($sort_key_sql['y'])) {
         $sql = "SELECT u.user_id
                 FROM " . USERS_TABLE . " u
                 $sql_from
                 LEFT JOIN " . PROFILE_FIELDS_DATA_TABLE . " pf ON (u.user_id = pf.user_id)
                 WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
                 $sql_where
                 ORDER BY $order_by";
       } else {

Codejunkie says he/she only had one field to sort so he used an "if" statement. I cannot figure out how to add another condition to the statement. I have tried elseif and looked at switch, but don't understand php code and have spent about 5 hours trying. I know that the ['y'] is unique to only one field and I compensated for that by added all of the other code corrections in twice, once for my first name field and another for my last name field. I assigned first name with an "x". But no matter what I do, clicking the sort link in either field always sorts whatever is "y". So I assume it goes back to the code I pasted above and not having another condition to sort for "x".

HELP

........

well i had two custom fields added but only one sorting . . . tonight, i went and tried to make the second one show in the memberlist and have sort . . and it worked . .

i did everything outlined for the first custom field for the second . .

BUT . . that block of code above? i DID NOT do any additional edits to it for the second field and it's sorting just fine.

[codejunkie]

Code: Select all

if(isset($sort_key_sql['y'])) {
         $sql = "SELECT u.user_id
                 FROM " . USERS_TABLE . " u
                 $sql_from
                 LEFT JOIN " . PROFILE_FIELDS_DATA_TABLE . " pf ON (u.user_id = pf.user_id)
                 WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
                 $sql_where
                 ORDER BY $order_by";
       } else {

...

well i had two custom fields added but only one sorting . . . tonight, i went and tried to make the second one show in the memberlist and have sort . . and it worked . .

i did everything outlined for the first custom field for the second . .

BUT . . that block of code above? i DID NOT do any additional edits to it for the second field and it's sorting just fine.

The code you quoted from kpence73, is in regards to viewing groups. It may also be due to changing the default sort to one of the custom fields. If you leave the default sort to the original sort column, you may not have a problem.

[redryder]

Code: Select all

if(isset($sort_key_sql['y'])) {
         $sql = "SELECT u.user_id
                 FROM " . USERS_TABLE . " u
                 $sql_from
                 LEFT JOIN " . PROFILE_FIELDS_DATA_TABLE . " pf ON (u.user_id = pf.user_id)
                 WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
                 $sql_where
                 ORDER BY $order_by";
       } else {

...

well i had two custom fields added but only one sorting . . . tonight, i went and tried to make the second one show in the memberlist and have sort . . and it worked . .

i did everything outlined for the first custom field for the second . .

BUT . . that block of code above? i DID NOT do any additional edits to it for the second field and it's sorting just fine.

The code you quoted from kpence73, is in regards to viewing groups. It may also be due to changing the default sort to one of the custom fields. If you leave the default sort to the original sort column, you may not have a problem.

the code i quoted was originally posted by codejunkie as needing to be added to sort the memberlist custom field columns . . he had stated that it worked for ONE custom field but was not completely sure if 'that' block of code would need an 'if' statement when trying to sort 'two' custom fields . . .

i was simply reporting that, yes, it works for a single custom field . . and that i did not have to add any additional statements or edit any further code to that particular block of code to make it sort 'two' custom fields so long as i went and edited everything else he had said to do.

i'm not having any problems . . as i stated . . it's working just fine! just thought it would be good to let everyone else reading this post that it worked for me.

[tonydeni]

I have followed all the post... WOW...

I got things to work for the most part. But here is where I can't seem to get the sort to work for the groups list.

I added the

Code: Select all

if( $mode != 'group' && (isset($sort_key_sql['tt']) || ...

to stop this check if viewing the group list.

But when i try to sort in the group list i get...

Code: Select all

SQL ERROR [ mysql4 ]

Unknown column 'cpfsorting.pf_company_name' in 'order clause' [1054]

SQL

SELECT u.user_id FROM phpbb_users u , phpbb_user_group ug WHERE u.user_type IN (0, 3) AND ug.user_pending = 0 AND u.user_id = ug.user_id AND ug.group_id = 8 ORDER BY ug.group_leader DESC, cpfsorting.pf_company_name ASC LIMIT 25

BACKTRACE


FILE: includes/db/mysql.php
LINE: 158
CALL: dbal->sql_error()

FILE: includes/db/mysql.php
LINE: 205
CALL: dbal_mysql->sql_query()

FILE: includes/db/dbal.php
LINE: 159
CALL: dbal_mysql->_sql_query_limit()

FILE: memberlist.php
LINE: 1438
CALL: dbal->sql_query_limit()

Anybody else having trouble sorting a group list?

Thanks
TonyD.

[tonydeni]

OK.. forget it.. I got it..

I tried brf's idea for parens....

i removed

Code: Select all

$mode != 'group' && 

And replaced

Code: Select all

SELECT u.user_id
                 FROM " . USERS_TABLE . " u
                 $sql_from
                 LEFT JOIN

with

Code: Select all

SELECT u.user_id
                 FROM (" . USERS_TABLE . " u
                 $sql_from)
                 LEFT JOIN

And it works great now...

Thanks
TonyD.

[MartectX]

Some configurations of Mysql do not handle mixing implicit joins with explicit joins.
Try putting parenthesis here:

Code: Select all

FROM (phpbb_users u , phpbb_user_group ug) LEFT JOIN 

You were right on the money there, thank you very much Brf!!!

[deepack]

Link to MODX file for custom profile field sort on memberlist page.

Thanks for this great MOD !!!

[worldzfree]

Hello,

I have read through this thread a few times trying to get sortable custom profile fields to work in the memberlist but I am stuck at the very end. I can get the columns to display and populate but when I go to sort I get the following error. Does anyone have any idea?

Code: Select all

SQL ERROR [ mysql4 ]

Unknown column 'fd.pf_user_last_name' in 'order clause' [1054]

SQL

SELECT u.user_id FROM phpbb_users u LEFT JOIN phpbb_profile_fields_data pf ON (u.user_id = pf.user_id) WHERE u.user_type IN (0, 3) ORDER BY fd.pf_user_last_name ASC LIMIT 50

BACKTRACE

FILE: includes/db/mysql.php
LINE: 158
CALL: dbal->sql_error()

FILE: includes/db/mysql.php
LINE: 205
CALL: dbal_mysql->sql_query()

FILE: includes/db/dbal.php
LINE: 159
CALL: dbal_mysql->_sql_query_limit()

FILE: memberlist.php
LINE: 1336
CALL: dbal->sql_query_limit()

[worldzfree]

Disregard. I was using kpence73's code sample for two fields and I spaced the fact they were using a custom "pf". Great little mod. Codejunkie, can you update your ModX to take into consideration Brf's initial edits? Would probably save other people some time (this jackass didn't read the thread from the beginning). Thanks for the mod!

Hello,

I have read through this thread a few times trying to get sortable custom profile fields to work in the memberlist but I am stuck at the very end. I can get the columns to display and populate but when I go to sort I get the following error. Does anyone have any idea?

Code: Select all

SQL ERROR [ mysql4 ]

Unknown column 'fd.pf_user_last_name' in 'order clause' [1054]

SQL

SELECT u.user_id FROM phpbb_users u LEFT JOIN phpbb_profile_fields_data pf ON (u.user_id = pf.user_id) WHERE u.user_type IN (0, 3) ORDER BY fd.pf_user_last_name ASC LIMIT 50

BACKTRACE

FILE: includes/db/mysql.php
LINE: 158
CALL: dbal->sql_error()

FILE: includes/db/mysql.php
LINE: 205
CALL: dbal_mysql->sql_query()

FILE: includes/db/dbal.php
LINE: 159
CALL: dbal_mysql->_sql_query_limit()

FILE: memberlist.php
LINE: 1336
CALL: dbal->sql_query_limit()

[gi-jones]

Ok, I did a little tweaking of my own, but I'm not an SQL guru, so please let me know why/if this is wrong.

This is the bit of code that CodeJunkie, so generously provided to be placed in the memberlist.php (including the original sql statement):

Code: Select all

if(isset($sort_key_sql['y'])) {
         $sql = "SELECT u.user_id
                 FROM " . USERS_TABLE . " u
                 $sql_from
                 LEFT JOIN " . PROFILE_FIELDS_DATA_TABLE . " fd ON (u.user_id = fd.user_id)
                 WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
                 $sql_where
                 ORDER BY $order_by";
      } else {
$sql = "SELECT u.user_id
			FROM " . USERS_TABLE . " u
				$sql_from
			WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
				$sql_where
			ORDER BY $order_by";
}

This is generally great, but I needed a way to make more than one user column orderable. So what I did was to put the "LEFT JOIN" line directly into to original sql statement like this:

Code: Select all

$sql = "SELECT u.user_id
			FROM " . USERS_TABLE . " u
				$sql_from
			LEFT JOIN " . PROFILE_FIELDS_DATA_TABLE . " fd ON (u.user_id = fd.user_id)   
			WHERE u.user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ")
				$sql_where
			ORDER BY $order_by";

Now, obviously that's a more lean way of doing it, but is it right? I mean, to me it seems to work like a charm, but I might be missing something. Or I might be brilliant Please let me know.

[HarryMann]

Brf wrote:Some configurations of Mysql do not handle mixing implicit joins with explicit joins.
Try putting parenthesis here:

Code: Select all
FROM (phpbb_users u , phpbb_user_group ug) LEFT JOIN

Yes, I had that problem too, throwing a 1054 wobbly u.user_id in ON clause is not a column

This is when using MySQL V5 I am sure and possibly the downloadable code needs updating as this bracketing method should be backward compatible I believe... a lot are going to stumble on this otherwise.

NB. The rigorous way to fix it is to join both additional tables after the FROM clause using Explicit Joins...
but then $sql_from would need the prefixing ',' removed?

=== Great MOD though, many thanks ====

[Lisbon]

In memberlist_body.html
After

Code: Select all

<th class="posts"><a href="{U_SORT_POSTS}#memberlist">{L_POSTS}</a></th>

add

Code: Select all

<th>Full Name</th>

After

Code: Select all

		<td class="posts"><!-- IF memberrow.POSTS --><a href="{memberrow.U_SEARCH_USER}" title="{L_SEARCH_USER_POSTS}">{memberrow.POSTS}</a><!-- ELSE -->{memberrow.POSTS}<!-- ENDIF --></td>

add

Code: Select all

		<td><!-- IF memberrow.PROFILE_FULL_NAME_VALUE -->
 		{memberrow.PROFILE_FULL_NAME_VALUE}
 		<!-- ELSE -->&nbsp;<!-- ENDIF --></td>
 

This is for prosilver right?