Alpha Sort Custom Profile Field in Member List

[jpapadpapa]

Note: I came up with this very recently and am currently testing it on my board and running into issues after doing a member search. Please do not use this until I have it figured out. If you can help me figure out what is wrong, I would appreciate it! I am very new at this.

Well, I can't believe I have actually done something worthy of being posted. I have shocked myself.;) Admittedly, I did have a little help from my husband. (Thanks, Honey!)

Anyway, this is a modification for sorting a custom profile field by the letter/alpha sort at the top of the member list. Some forums, such as mine, are better sorted by another field. My forum is for a local group of people who already know one another. So, it makes more sense for them to see everyone alphabetized by their real names. However, we also wanted to allow everyone to pick their own usernames, rather than require them to use real names.

To do this, you must first use this hack (https://vborg.vbsupport.ru/showthread.php?t=124580) to allow your custom field to be shown (without showing all of your custom fields) in the member list.

Once you have that working, you can move it to the first column by changing this code in the memberlist.php (thanks to Andreas for this part):

First, find this:

default:
$sqlsort = 'user.username';
$sortfield = 'username';


Replace it with this (where "X" is the custom field you want to appear and "lastname" is the name of that field):

default:
$sqlsort = 'userfield.fieldX';
$sortfield = 'lastname'


Then, find this:

// set defaults and sensible values

if ($sortfield == '')
{
$sortfield = 'username';
}


Replace it with this (where "lastname" is the name of the profile field you are using):

// set defaults and sensible values

if ($sortfield == '')
{
$sortfield = 'lastname';
}


Now you should have your specific custom field listed on your member list as the first column. Finally, to make it sort by the alpha list at the top, you will need to do the following:

Find this code in the memberlist.php:

if ($ltr != '')
{
if ($ltr == '#')
{
$condition = "username NOT REGEXP(\"^[a-zA-Z]\")";
}
else
{
$ltr = chr(intval(ord($ltr)));
$condition = 'username LIKE("' . $db->escape_string_like($ltr) . '%")';
}
}


Replace it with this (again, using your specific custom field number and name instead of "fieldX" and "lastname"):

if ($ltr != '')
{
if ($ltr == '#')
{
if ($sortfield == 'lastname')
{
$condition = "fieldX NOT REGEXP(\"^[a-zA-Z]\")";
}
else
{
$condition = "username NOT REGEXP(\"^[a-zA-Z]\")";
}
}
else
{
$ltr = chr(intval(ord($ltr)));

if ($sortfield == 'lastname')
{
$condition = 'fieldX LIKE("' . $db->escape_string_like($ltr) . '%")';
}
else
{
$condition = 'username LIKE("' . $db->escape_string_like($ltr) . '%")';
}
}
}

That's it! I hope this helps someone else. If there are any problems with it, let me know. It was quite a process for this newbie to figure it all out and it's possible I may have missed a step here.;)

[aproape]

This sounds very useful! Thanks - we'll try it out very soon!

*poof*
aproape

[anthonym16]

Thanks, this post did help me. I wish there was a way to do this in a plug-in... There just aren't enough hooks to do it though.

To get the search working, you need to find the block of code starting with the IF statement: if ($vbulletin->GPC['ausername']).

When a quick search is done, the field 'ausername' contains the search string. All you need to do is update the condition variable to search fieldX rather than username.


if ($vbulletin->GPC['ausername'])
{
if($sortfield == 'lastname')
{
$condition .= " AND userfield.fieldX LIKE '%" . $db->escape_string_like(htmlspecialchars_uni($vbulletin->GPC['ausername'])) . "%' ";
}
else
{
$condition .= " AND username LIKE '%" . $db->escape_string_like(htmlspecialchars_uni($vbulletin->GPC['ausername'])) . "%' ";
}
}

Note that I wrote this using vb 3.8.3.

[ganima]

There seems to be a problem with the field name. I get this MySQL error:
(...) Unknown column 'field7' in 'where clause' (...)

That is the part of my code:
if ($sortfield == 'nachname')
{
$condition = 'field7 LIKE("' . $db->escape_string_like($ltr) . '%")';
}
else
{
$condition = 'username LIKE("' . $db->escape_string_like($ltr) . '%")';
}

What's wrong with that? field7 is correct but the field name is not. I also tried to insert a variable like $userinfo[field7].

What is the right syntax here for the field name? Can anybody help?

[anthonym16]

Prefix field7 with the table name. userfield.field7

There are a lot of joins in that query so you need to specify which table field7 comes from.