[TIP/TRICK] How to show Custom Profile Fields in the Members List (with mod

[thezkitten]

How to add your Custom Profile Fields  into the memberlist in SMF1.1.x. 
Original Topic here


Tip/Trick:

I was adding a custom URL, so this is setup like the 'website' column, but can easily be adapted to text information.

First, you have to find the Custom Profile number for the item you want to show (it's the CP#)
My "Custom Profile Field ID"s were in the database under
"smf_themes"
To find the specific one I needed, I just exported the table, searched for a string that I knew was in the spot I wanted, and I had my number.
If you're still not finding it, then modify a profile with something that is unique (like a limerick or something) you just need it in there long enough to find the CP# you're looking for.


Now, you need to label your new column, I went to "\Themes\default\languages\index.english.php"
and just plugged in

Code Select Expand

$txt['yourtextcode%'] = '%yourlabel%';
I don't think it matters where (I put mine under MSN) but do make sure you use a unique code, or it could mess something up!


Then, I went to "\Sources\memberlist.php"
found the columns

Code Select Expand


   // Set up the columns...
   $context['columns'] = array(
      'isOnline' => array(
         'label' => $txt['online8'],
         'width' => '20'
      ),
      'realName' => array(
         'label' => $txt[35]
      ),
      'emailAddress' => array(
         'label' => $txt[307],
         'width' => '25'
      ),
      'websiteUrl' => array(
         'label' => $txt[96],
         'width' => '25'
      ),
      'ICQ' => array(
         'label' => $txt[513],
         'width' => '25'
      ),
      'AIM' => array(
         'label' => $txt[603],
         'width' => '25'
      ),
      'YIM' => array(
         'label' => $txt[604],
         'width' => '25'
      ),
      'MSN' => array(
         'label' => $txt['MSN'],
         'width' => '25'
      ),
      'ID_GROUP' => array(
         'label' => $txt[87]
      ),
      'registered' => array(
         'label' => $txt[233]
      ),
      'posts' => array(
         'label' => $txt[21],
         'width' => '115',
         'colspan' => '2'
      )
   );
   
and I added mine to the end, making sure that all my parentheses and semi-colons were correct
and also making sure I used the code I plugged into the index file

Code Select Expand


      'posts' => array(
         'label' => $txt[21],
         'width' => '115',
         'colspan' => '2'
      ),
      '%yourCPnumber%' => array(
         'label' => $txt['%yourtextcode%'],
         'width' => '25',
      )
);

Then I went to  "\Themes\default\memberlist.template.php"

And I found where the info was-

Code Select Expand


   // Assuming there are members loop through each one displaying their data.
   if (!empty($context['members']))
   {
      foreach ($context['members'] as $member)
         echo '
      <tr style="text-align: center;"', empty($member['sort_letter']) ? '' : ' id="letter' . $member['sort_letter'] . '"', '>
         <td class="windowbg2">
            ', $context['can_send_pm'] ? '<a href="' . $member['online']['href'] . '" title="' . $member['online']['text'] . '">' : '', $settings['use_image_buttons'] ? '<img src="' . $member['online']['image_href'] . '" alt="' . $member['online']['text'] . '" align="middle" />' : $member['online']['label'], $context['can_send_pm'] ? '</a>' : '', '
         </td>
         <td class="windowbg" align="left">', $member['link'], '</td>
         <td class="windowbg2">', $member['hide_email'] ? '' : '<a href="mailto:' . $member['email'] . '"><img src="' . $settings['images_url'] . '/email_sm.gif" alt="' . $txt[69] . '" title="' . $txt[69] . ' ' . $member['name'] . '" /></a>', '</td>
         <td class="windowbg">', $member['website']['url'] != '' ? '<a href="' . $member['website']['url'] . '" target="_blank"><img src="' . $settings['images_url'] . '/www.gif" alt="' . $member['website']['title'] . '" title="' . $member['website']['title'] . '" /></a>' : '', '</td>
         <td class="windowbg2">', $member['icq']['link'], '</td>
         <td class="windowbg2">', $member['aim']['link'], '</td>
         <td class="windowbg2">', $member['yim']['link'], '</td>
         <td class="windowbg2">', $member['msn']['link'], '</td>
         <td class="windowbg" align="left">', empty($member['group']) ? $member['post_group'] : $member['group'], '</td>
         <td class="windowbg" align="left">', $member['registered_date'], '</td>
         <td class="windowbg2" width="15">', $member['posts'], '</td>
         <td class="windowbg" width="100" align="left">
            ', $member['posts'] > 0 ? '<img src="' . $settings['images_url'] . '/bar.gif" width="' . $member['post_percent'] . '" height="15" alt="" />' : '', '</td>
     
and added my code after the other listings, and before the closing <tr>

Code Select Expand


         <td class="windowbg2">', !empty($member['options']['%yourCPnumber%']) != '' ? '<a href="' . $member['options']['%yourCPnumber%'] . '" target="_blank"><img src="%yourimagelocation%"></a>' : '', '</td>

at the end

Then (still in Memberlist.template.php) I did a search of all

Code Select Expand


colspan="12 and replaced them with

Code Select Expand


colspan="13"
save & upload your files and you're good.

My code had custom stuff in it, the image specificly. To have something without the image you would just adjust accordingly..
If you have very little knowledge of PHP (like me) then here's a couple other options, just plug in your CP information-
For websites/urls

Code Select Expand


      <td class="windowbg2">', !empty($member['options']['%yourCPnumber%']) != '' ? '<a href="' . $member['options']['%yourCPnumber%'] . '" target="_blank"><img src="%yourimagelocation%"></a>' : '', '</td>

and this for text

Code Select Expand


<td class="windowbg2">', !empty($member['options']['%yourCPnumber%']) != '' ? '<a href="' . $member['options']['%yourCPnumber%'] . '" target="_blank">%yourtexthere%</a>' : '', '</td>


hope I could help!

[fals]

Thanks - it works !
But i need the find out how the sort the colum, and add search...

I try some things tomorrow

[thezkitten]

the original thread had some information about it, I spent a few hours on this one today, so I'll leave the search function for tomorrow (if not tonight)
It's basiclly just adding it into the search part of .. the memberlist.template and memberlist (I'm pretty sure)

[Marcus Forsberg]

thezkitten, would you like this topic to be moved to Tips and Tricks?

[Kermit]

I assume this mod is already  doing the same thing (in a more detailed way)

http://custom.simplemachines.org/mods/index.php?mod=1520

[thezkitten]

thezkitten, would you like this topic to be moved to Tips and Tricks?

If it's helpful, yes  thank you

I assume this mod is already  doing the same thing (in a more detailed way)

http://custom.simplemachines.org/mods/index.php?mod=1520

yes, but that's for 2.0, which has a custom profile fields option built in.
This tip/trick is for 1.1.x (which doesn't have that option) to go alone with this mod, that is used with 1.1.x 

[fals]

Have you found a way to add search abillity ?

I can seem to figure it out, since the "custom data" is saved in db smf_themes and not he usual smf_members...

Normally i use this method: http://www.simplemachines.org/community/index.php?topic=304711.msg2015971#msg2015971

[[Core]]

Nicet thanks.

[thezkitten]

Have you found a way to add search abillity ?

I can seem to figure it out, since the "custom data" is saved in db smf_themes and not he usual smf_members...

Normally i use this method: http://www.simplemachines.org/community/index.php?topic=304711.msg2015971#msg2015971

I think I've about got it, I *should* have it posted up after the weekend (fireworks you know)

Nicet thanks.

welcome!

[[SiNaN]]

You need to add a sort method for your field too. Otherwise it will give a database error when you click on your new column name. An example on how to add a sort method:

http://www.simplemachines.org/community/index.php?topic=269806.msg1772280#msg1772280

[rpd1960]

I have succesfully added three colums which appear on my memberlist, but I'm having trouble with the sort feature.

This is what I have added to my "Memberlist.template.php"

        <td class="windowbg" align="left">', $member['options']['CP1'], '</td>
         <td class="windowbg" align="left">', $member['options']['CP2'], '</td>
         <td class="windowbg" align="left">', $member['options']['CP3'], '</td>

This is what I have added to my "Memberlist.php"

'CP1' => array(
   'label' => $txt['CP1'],
                        'width' => '25'
  ),
  'CP2' => array(
   'label' => $txt['CP2'],
                        'width' => '25'
  ),
  'CP3' => array(
   'label' => $txt['CP3'],
                        'width' => '25'
  ),


So far, this works perfectly and shows up on my memberlist. My problem is with the sort.

This is what I have tried as sort in my "Memberlist.php":

'CP1' => array(
   'down' => 'CP1 ASC',
   'up' => 'CP1 DESC'
  ),
  'CP2' => array(
   'down' => 'mem.CP2 ASC',
   'up' => 'mem.CP2 DESC'
  ),
  'CP3' => array(
   'down' => 'themes.CP3 ASC',
   'up' => 'themes.CP3 DESC'
  ),

But when I click on the sort field in my memberlist, I get this message:

Unknown column 'CP1' in 'order clause'
File: /home/guysinto/public_html/GiJ/Sources/Memberlist.php
Line: 368

Can someone tell me what I am doing wrong

Thanks in advance

BTW, I'm using SMF 1.1.11

[Arantor]

Where do you get CP1 through CP3 from? Those need to be fields directly linked in the query, which they don't really appear to be right now (unless you have a CP2 column in the members table, a CP3 column in the themes table with an appropriate join, and a CP1 column in any of the linked tables, which you don't)

[Arantor]

Hmm. Do you get the same error on clicking on the other headings?

[rpd1960]

I'm not a php expert. I found that code in the first message of this feed.

Then where "thezkitten" speaks of solving her issue with the sort problem, the feed stops there. So I tried differents things myself, but was never able to get it to work.

I'm using "Custom Profile Fields" which are CP1, CP2 & CP3 in my themes database

They are displayed correctly in my memberlist, but I can't figure out how to sort them.

I don't know how to tell the sort to get the fields from the theme database.

Yes, I get the same errors with all three

I tried to attach a print screen of my data base here, but it doesn't permit it :-(

[Arantor]

I haven't tested this, and don't really want to get into supporting this, but I think you could make it work like so, then be able to use "th.value" as your order variable, same for all three fields.

Code (find) Select Expand

$request = db_query("
SELECT mem.ID_MEMBER
FROM {$db_prefix}members AS mem" . ($_REQUEST['sort'] === 'isOnline' ? "
LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
WHERE mem.is_activated = 1" . (empty($where) ? '' : "
AND $where") . "
ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);

Code (replace) Select Expand


switch($_REQUEST['sort'])
{
case 'CP1':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND t1.variable = 'CP1')"; break;
case 'CP2':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND t1.variable = 'CP2')"; break;
case 'CP3':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND t1.variable = 'CP3')"; break;
default:
$cpsort = "";
}

$request = db_query("
SELECT mem.ID_MEMBER, t1.value AS CP1, t2.value AS CP2, t3.value AS CP3
FROM {$db_prefix}members AS mem" . $cpsort . ($_REQUEST['sort'] === 'isOnline' ? "
LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
WHERE mem.is_activated = 1" . (empty($where) ? '' : "
AND $where") . "
ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);

[rpd1960]

I'll try that out and let you know

Thank you very much ;-)

[rpd1960]

I tried it and now I get this error message :-(

Unknown column 't1.value' in 'field list'
File: /home/guysinto/public_html/GiJ/Sources/Memberlist.php
Line: 355

[Arantor]

My bad for not testing and not being awake at the time.

Code Select Expand


switch($_REQUEST['sort'])
{
case 'CP1':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND th.variable = 'CP1')";
$field = ", th.value AS value";
break;
case 'CP2':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND th.variable = 'CP2')";
$field = ", th.value AS value";
break;
case 'CP3':
$cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID_MEMBER AND th.variable = 'CP3')";
$field = ", th.value AS value";
break;
default:
$cpsort = "";
$field = ', "" as value';
}

$request = db_query("
SELECT mem.ID_MEMBER" . $field . "
FROM {$db_prefix}members AS mem" . $cpsort . ($_REQUEST['sort'] === 'isOnline' ? "
LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
WHERE mem.is_activated = 1" . (empty($where) ? '' : "
AND $where") . "
ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);

Then the sort by thing should be simply 'value' rather than 'th.value'.

[rpd1960]

I'm getting a different error this time, so I'll repost what I have changed in "/Sources/Memberlist.php", incase I'm doing something wrong:

===========================================================
   // Set up the columns...
   $context['columns'] = array(
      'isOnline' => array(
         'label' => $txt['online8'],
         'width' => '20'
      ),
      'memberName' => array(
         'label' => $txt[35]
      ),
      'location' => array(
         'label' => $txt['location']
      ),
      'CP1' => array(
         'label' => $txt['CP1'],
                        'width' => '25'
      ),
      'CP2' => array(
         'label' => $txt['CP2'],
                        'width' => '25'
      ),
      'CP3' => array(
         'label' => $txt['CP3'],
                        'width' => '25'
      ),
      'emailAddress' => array(
         'label' => $txt[307],
         'width' => '25'
      ),
      'websiteUrl' => array(
         'label' => $txt[96],
         'width' => '25'
      ),
         'avatar' => array(
            'label' => $txt['avatar'],
      ),
      'ID_GROUP' => array(
         'label' => $txt[87]
      ),
      'lastonline' => array(
         'label' => $txt[233]
      ),
      'posts' => array(
         'label' => $txt[21],
         'width' => '115',
         'colspan' => '2'
      )
   );

==============================================================
   // List out the different sorting methods...
   $sort_methods = array(
      'isOnline' => array(
         'down' => '(ISNULL(lo.logTime)' . (!allowedTo('moderate_forum') ? ' OR NOT mem.showOnline' : '') . ') ASC, memberName ASC',
         'up' => '(ISNULL(lo.logTime)' . (!allowedTo('moderate_forum') ? ' OR NOT mem.showOnline' : '') . ') DESC, memberName DESC'
      ),
      'memberName' => array(
         'down' => 'mem.memberName ASC',
         'up' => 'mem.memberName DESC'
      ),
      'location' => array(
         'down' => 'mem.location ASC',
         'up' => 'mem.location DESC'
      ),
      'CP1' => array(
         'down' => 'CP1 ASC',
         'up' => 'CP1 DESC'
      ),
      'CP2' => array(
         'down' => 'CP2 ASC',
         'up' => 'CP2 DESC'
      ),
      'CP3' => array(
         'down' => 'CP3 ASC',
         'up' => 'CP3 DESC'
      ),
      'emailAddress' => array(
         'down' => (allowedTo('moderate_forum') || empty($modSettings['allow_hideEmail'])) ? 'mem.emailAddress ASC' : 'mem.hideEmail ASC, mem.emailAddress ASC',
         'up' => (allowedTo('moderate_forum') || empty($modSettings['allow_hideEmail'])) ? 'mem.emailAddress DESC' : 'mem.hideEmail DESC, mem.emailAddress DESC'
      ),
      'websiteUrl' => array(
         'down' => 'LENGTH(mem.websiteURL) > 0 DESC, ISNULL(mem.websiteURL) ASC, mem.websiteURL ASC',
         'up' => 'LENGTH(mem.websiteURL) > 0 ASC, ISNULL(mem.websiteURL) DESC, mem.websiteURL DESC'
      ),
      'lastonline' => array(
         'down' => 'mem.lastLogin ASC',
         'up' => 'mem.lastLogin DESC'
      ),
      'ID_GROUP' => array(
         'down' => 'ISNULL(mg.groupName) ASC, mg.groupName ASC',
         'up' => 'ISNULL(mg.groupName) DESC, mg.groupName DESC'
      ),
      'posts' => array(
         'down' => 'mem.posts DESC',
         'up' => 'mem.posts ASC'
      )
   );
===================================================================
   // Select the members from the database.
   switch($_REQUEST['sort'])
   {
                case 'CP1':
         $cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID [nofollow]_MEMBER AND th.variable = 'CP1')";
                        $field = ", th.value AS value";
                        break;
                case 'CP2':
         $cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID [nofollow]_MEMBER AND th.variable = 'CP2')";
                        $field = ", th.value AS value";
                        break;
                case 'CP3':
         $cpsort = " LEFT JOIN {$db_prefix}themes AS th ON (th.ID_MEMBER = mem.ID [nofollow]_MEMBER AND th.variable = 'CP3')";
                        $field = ", th.value AS value";
                        break;
           default:
         $cpsort = "";
                        $field = ', "" as value';
        }
===================================================================
The error I get now, when I click on any on the three colums is:

Unknown column 'CP1' in 'order clause'
File: /home/guysinto/public_html/GiJ/Sources/Memberlist.php
Line: 363

Unknown column 'CP2' in 'order clause'
File: /home/guysinto/public_html/GiJ/Sources/Memberlist.php
Line: 363

Unknown column 'CP3' in 'order clause'
File: /home/guysinto/public_html/GiJ/Sources/Memberlist.php
Line: 363

[Arantor]

Huh?

Couple of things missing...

be able to use "th.value" as your order variable, same for all three fields.

Then the sort by thing should be simply 'value' rather than 'th.value'.

Thus:

Code (find) Select Expand

      'CP1' => array(
         'down' => 'CP1 ASC',
         'up' => 'CP1 DESC'
      ),
      'CP2' => array(
         'down' => 'CP2 ASC',
         'up' => 'CP2 DESC'
      ),
      'CP3' => array(
         'down' => 'CP3 ASC',
         'up' => 'CP3 DESC'
      ),

Code (replace) Select Expand

      'CP1' => array(
         'down' => 'value ASC',
         'up' => 'value DESC'
      ),
      'CP2' => array(
         'down' => 'value ASC',
         'up' => 'value DESC'
      ),
      'CP3' => array(
         'down' => 'value ASC',
         'up' => 'value DESC'
      ),