Topic: Roster

[Hoodie]

I know, I know.  Your going to say that this has been discussed many times here and everywhere about SMF.  Also that there is a mod capable of doing this here: http://custom.simplemachines.org/mods/index.php?mod=2167

Well, I haven't found anything to work the way I want.  I've tried looking at the code that has been given in different places and modifying it to my needs but it's not working.  Plus I have some custom fields that I want included and haven't seen that requested.

Here is the information that I would like (screenshot with formatting as well as html code)

Code: [Select]

<div id="mlist" class="tborder topic_table">
<div class="title_bar">
<h3 class="titlebg"><center>The Iron Throne</center></h3>
</div>
<table class="table_grid" cellspacing="0" width="100%">
<thead>
<tr class="catbg">
<th scope="col" class="" style="width: auto;" nowrap="nowrap">
Username
</th>
<th scope="col" class="" style="width: auto;" nowrap="nowrap">
Position
</th>
<th scope="col" class="" style="width: auto;" nowrap="nowrap">
GameCenter ID
</th>
<th scope="col" class="" style="width: auto;" nowrap="nowrap">
KiK ID
</th>
<th scope="col" class="" style="width: auto;" nowrap="nowrap">
Member Since
</th>
</tr>
</thead>
<tbody>
<tr>
<td class="windowbg">Members Username</td>
<td class="windowbg">Members Usergroup</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Date Registered, MM-DD-YYYY</td>
</tr>
<tr>
<td class="windowbg">Members Username</td>
<td class="windowbg">Members Usergroup</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Date Registered, MM-DD-YYYY</td>
</tr>
<tr>
<td class="windowbg">Members Username</td>
<td class="windowbg">Members Usergroup</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Date Registered, MM-DD-YYYY</td>
</tr>
<tr>
<td class="windowbg">Members Username</td>
<td class="windowbg">Members Usergroup</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Custom Field</td>
<td class="windowbg">Date Registered, MM-DD-YYYY</td>
</tr>
</tbody>
</table>
</div>
So the only information that I'm trying to pull is Username, Membergroup, Registration Date (format it as shown).  As for the custom fields, they are as follows:

cust_gamece
cust_kikmes

Only other request is that the membergroups be sorted by the numerical order that I put them in on the listing to be included, e.g.; array(1,34,44,33,39,24,32) would display:

All members in group 1
All members in group 34
All members in group 44
All members in group 33
All members in group 39
All members in group 24
All members in group 32

Thanks for your help if anyone can help me.

[Old Fossil]

This looks more like it belongs more on smf than it does on SP.

 

[la muerte]

This looks more like it belongs more on smf than it does on SP.

 

Perhaps, but people here should have plenty of knowledge to get it done easily.
I'm sure if users can request custom blocks they can also request custom pages .

It would be nice if someone could create some custom clan/guild blocks and pages for SP such as rosters, upcoming events (e.g. during this month), ...

[Hoodie]

This looks more like it belongs more on smf than it does on SP.

 

Perhaps, but people here should have plenty of knowledge to get it done easily.
I'm sure if users can request custom blocks they can also request custom pages .

It would be nice if someone could create some custom clan/guild blocks and pages for SP such as rosters, upcoming events (e.g. during this month), ...

Agreed that this is for a custom page that will be implemented through the SimplePortal pages function.  I don't see this going to the SMF site as that would require a mod to be able to display this information somewhere.  I am asking here because I am using a SimplePortal page to display this information.

[Old Fossil]

I've been using SP since way back when, I only started using the pages last year.

 

[la muerte]

Actually I think SP is by far the best portal out there.
Clan integration: roster, fixtures, results, ... Is something I'd happily donate good money for. Not that money means anything but still :p

[Mick.]

Actually I think SP is by far the best portal out there.
Clan integration: roster, fixtures, results, ... Is something I'd happily donate good money for. Not that money means anything but still :p

Money means everything lol j/k

[Hoodie]

Actually I think SP is by far the best portal out there.
Clan integration: roster, fixtures, results, ... Is something I'd happily donate good money for. Not that money means anything but still :p

I'm just looking for the roster page basically.  I don't need all the other stuff as it won't apply to the game that I'm using the forum for.  And it isn't really in the scope of a page for SimplePortal but more so a modification for SMF as a whole.

[Cal]

I do something similar by pulling a database query directly.  You could try something like this in a custom PHP page:

Code: [Select]

global $smcFunc;

echo '
                <div id="mlist" class="tborder topic_table">
                        <div class="title_bar">
                                <h3 class="titlebg"><center>The Iron Throne</center></h3>
                        </div>
                        <table class="table_grid" cellspacing="0" width="100%">
                                <thead>
                                        <tr class="catbg">
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Username
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Position
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        GameCenter ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        KiK ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Member Since
                                                </th>
                                        </tr>
                                </thead>
                                <tbody>';

$request = $smcFunc['db_query']('' ,'
        SELECT m.real_name, g.group_name, GROUP_CONCAT(t.value ORDER BY t.variable SEPARATOR "</td><td>") AS custom_fields, m.date_registered
        FROM {db_prefix}members AS m
                LEFT JOIN {db_prefix}themes AS t ON m.id_member=t.id_member
                LEFT JOIN {db_prefix}membergroups AS g ON m.id_group = g.id_group
        WHERE t.variable IN ("cust_gamece", "cust_kikmes")
        GROUP BY m.real_name
        ORDER BY find_in_set(m.id_group, "1,34,44,33,39,24,32"), m.real_name',
        array(
        )
 );

if (!$request)
        db_fatal_error();

        while ($row = $smcFunc['db_fetch_assoc']($request)) {
                echo '
                                                <tr>
                                                        <td class="windowbg">', $row["real_name"], '</td>
                                                        <td class="windowbg">', $row["group_name"], '</td>
                                                        <td class="windowbg">', $row["custom_fields"], '</td>
                                                        <td class="windowbg">', date("m-d-Y",$row["date_registered"]), '</td>
                                                </tr>';
        }

$smcFunc['db_free_result']($request);

        echo '
                                </tbody>
                        <table>
                <div>';


[FireDitto]

@Cal, you forgot to close the 'table' in the second last line.

Code: [Select]

global $smcFunc;

echo '
                <div id="mlist" class="tborder topic_table">
                        <div class="title_bar">
                                <h3 class="titlebg"><center>The Iron Throne</center></h3>
                        </div>
                        <table class="table_grid" cellspacing="0" width="100%">
                                <thead>
                                        <tr class="catbg">
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Username
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Position
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        GameCenter ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        KiK ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Member Since
                                                </th>
                                        </tr>
                                </thead>
                                <tbody>';

$request = $smcFunc['db_query']('' ,'
        SELECT m.real_name, g.group_name, GROUP_CONCAT(t.value ORDER BY t.variable SEPARATOR "</td><td>") AS custom_fields, m.date_registered
        FROM {db_prefix}members AS m
                LEFT JOIN {db_prefix}themes AS t ON m.id_member=t.id_member
                LEFT JOIN {db_prefix}membergroups AS g ON m.id_group = g.id_group
        WHERE t.variable IN ("cust_gamece", "cust_kikmes")
        GROUP BY m.real_name
        ORDER BY find_in_set(m.id_group, "1,34,44,33,39,24,32"), m.real_name',
        array(
        )
 );

if (!$request)
        db_fatal_error();

        while ($row = $smcFunc['db_fetch_assoc']($request)) {
                echo '
                                                <tr>
                                                        <td class="windowbg">', $row["real_name"], '</td>
                                                        <td class="windowbg">', $row["group_name"], '</td>
                                                        <td class="windowbg">', $row["custom_fields"], '</td>
                                                        <td class="windowbg">', date("m-d-Y",$row["date_registered"]), '</td>
                                                </tr>';
        }

$smcFunc['db_free_result']($request);

        echo '
                                </tbody>
                        </table>
                <div>';


[Cal]

Ha, thank you!  That's what I get for putting someone else's styling in  by hand. 

[Hoodie]

Thanks for the code, Cal.  There is a few issues though.



The formatting is off if the person doesn't have one of the custom fields filled out.  As this is not a mandatory field, it needs to be flexible to if the member has inputted it or not.  Also, it is not filtering by the membergroups that I put into the code here:

Code: [Select]

        ORDER BY find_in_set(m.id_group, "1,34,44,33,39,24,32"), m.real_name',
It has the right ones all down at the bottom of the page but lists everyone else above that.

[Cal]

If you want to show only certain member groups as well as sort in a particular order, then just add that to the WHERE declaration in the SQL: 

Code: [Select]

WHERE t.variable IN ("cust_gamece", "cust_kikmes") AND m.id_group IN (1,34,44,33,39,24,32)
Fixing the empty strings is more challenging; I'll need to get back to you on that one.  Changing the select to

Code: [Select]

SELECT m.real_name, g.group_name, GROUP_CONCAT(CASE WHEN t.value = "" THEN "N/A" ELSE t.value END ORDER BY t.variable SEPARATOR "</td><td class=\"windowbg\">") AS custom_fields, m.date_registeredworks some of the time on my test data, but not all of the time.

[FireDitto]

... When I test it, all I get is the header bar. It's not pulling anything up for me..? o_O;

[Cal]

I used Hoodie's specific group IDs and t.variables. Without those in your data, the WHERE syntax will return no result (as it should).  Here's my test, pulling data specific to my fields.  Sometimes the CASE replaces like it should and sometimes it doesn't, though it seems like as long as the first custom field has data it'll be okay regardless.  It's probably something to do with a conflict between empty string and null, which are treated differently in MySQL.

[Hoodie]

If you want to show only certain member groups as well as sort in a particular order, then just add that to the WHERE declaration in the SQL: 

Code: [Select]

WHERE t.variable IN ("cust_gamece", "cust_kikmes") AND m.id_group IN (1,34,44,33,39,24,32)
Fixing the empty strings is more challenging; I'll need to get back to you on that one.  Changing the select to

Code: [Select]

SELECT m.real_name, g.group_name, GROUP_CONCAT(CASE WHEN t.value = "" THEN "N/A" ELSE t.value END ORDER BY t.variable SEPARATOR "</td><td class=\"windowbg\">") AS custom_fields, m.date_registeredworks some of the time on my test data, but not all of the time.

Like you said, that N/A works some of the time from what I can tell too.  It works perfectly otherwise.  I think I'm gonna interject by MySQL "N/A" into everyones field that is current that doesn't have that field blank by phpMyAdmin if that's possible.  Just wonder how to be able to do this for new members is the only thing.

[FireDitto]

I used Hoodie's specific group IDs and t.variables. Without those in your data, the WHERE syntax will return no result (as it should).  Here's my test, pulling data specific to my fields.

... Okay, would you mind explaining how I'd personalise it to work on my site? I don't know what a t.variable is, so I have no idea how to make it work, and I'd really like to use a roster similar to this.

[Cal]

Like you said, that N/A works some of the time from what I can tell too.  It works perfectly otherwise.  I think I'm gonna interject by MySQL "N/A" into everyones field that is current that doesn't have that field blank by phpMyAdmin if that's possible.  Just wonder how to be able to do this for new members is the only thing.

The good news is that I've figured out the problem, which is that if the forum profile has not been edited since the custom profile field was added (I actually do this all the time, which is probably not best practice, bad admin), there's no record for that account's custom field in the themes table.  If someone just hasn't filled out the field, it returns an empty string just fine.  Probably the best way to do this is to use some magic admin powers, open every applicable forum profile, and save it (without making any changes).  This will write an empty string to the database for that t.variable.  I think if you display the fields on registration it will do the same thing for new members as they come in, too.

Otherwise, you could have it display in a single field (change the SEPARATOR string to just a comma, say) and not worry about the table issue -- but it appears that if neither field has been set before, it's not pulling that member ID at all, so that may be a problem for you too.

FireDitto, I'll send you a PM. 

[FireDitto]

FireDitto, I'll send you a PM. 

Thank you!

Also, sorry about the apparent attitude in the previous post o.O it wasn't meant to sound snippy!

[Hoodie]

Like you said, that N/A works some of the time from what I can tell too.  It works perfectly otherwise.  I think I'm gonna interject by MySQL "N/A" into everyones field that is current that doesn't have that field blank by phpMyAdmin if that's possible.  Just wonder how to be able to do this for new members is the only thing.

The good news is that I've figured out the problem, which is that if the forum profile has not been edited since the custom profile field was added (I actually do this all the time, which is probably not best practice, bad admin), there's no record for that account's custom field in the themes table.  If someone just hasn't filled out the field, it returns an empty string just fine.  Probably the best way to do this is to use some magic admin powers, open every applicable forum profile, and save it (without making any changes).  This will write an empty string to the database for that t.variable.  I think if you display the fields on registration it will do the same thing for new members as they come in, too.

Otherwise, you could have it display in a single field (change the SEPARATOR string to just a comma, say) and not worry about the table issue -- but it appears that if neither field has been set before, it's not pulling that member ID at all, so that may be a problem for you too.

FireDitto, I'll send you a PM. 

I guess that will have to be the best way to make the field go to N/A is individually go to each profile and just hit modify profile and it will change the field.  Has worked for the few that I tested.  Displaying the fields on registration doesn't affect this value as I've had them both on the registration page since the start of the forum.

The good thing about not pulling the info if neither is set is the first field (cust_gamece) is on registration and is a required field so will always be filled out no matter what.  No issues there for me.

Thank you for all the help and definitely looking forward to implementing this throughout the site on 11 custom pages, basically the same encompassing 44 different membergroups.  Here is my final code just to be sure the most current information is at the bottom of the page:

Code: [Select]

<?php
global $smcFunc, $txt, $scripturl; 

echo '
                <div id="mlist" class="tborder topic_table">
                        <div class="title_bar">
                                <h3 class="titlebg"><center>The Iron Throne</center></h3>
                        </div>
                        <table class="table_grid" cellspacing="0" width="100%">
                                <thead>
                                        <tr class="catbg">
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Username
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Position
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        GameCenter ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        KiK ID
                                                </th>
                                                <th scope="col" class="" style="width: auto;" nowrap="nowrap">
                                                        Member Since
                                                </th>
                                        </tr>
                                </thead>
                                <tbody>';

$request = $smcFunc['db_query']('' ,'
SELECT m.real_name, m.id_member, g.group_name, GROUP_CONCAT(CASE WHEN t.value = "" THEN "N/A" ELSE t.value END ORDER BY t.variable SEPARATOR "</td><td class=\"windowbg\">") AS custom_fields, m.date_registered
        FROM {db_prefix}members AS m
                LEFT JOIN {db_prefix}themes AS t ON m.id_member=t.id_member 
                LEFT JOIN {db_prefix}membergroups AS g ON m.id_group = g.id_group 
WHERE t.variable IN ("cust_gamece", "cust_kikmes") AND m.id_group IN (21,22,23,24)
        GROUP BY m.real_name
        ORDER BY find_in_set(m.id_group, "21,22,23,24"), m.real_name',
        array(
        )
 );

if (!$request)
        db_fatal_error();

        while ($row = $smcFunc['db_fetch_assoc']($request)) {
                echo '
                                                <tr>
                                                        <td class="windowbg"><a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '" title="' . $txt['profile_of'] . ' ' . $row['real_name'] . '">' . $row['real_name'] . '</a></td>
                                                        <td class="windowbg">', $row["group_name"], '</td>
                                                        <td class="windowbg">', $row["custom_fields"], '</td>
                                                        <td class="windowbg">', date("m-d-Y",$row["date_registered"]), '</td>
                                                </tr>';
        }

$smcFunc['db_free_result']($request);

        echo '
                                </tbody>
                        </table>
                <div>';
?>

[Hoodie]

OK.  I know I said that I was done but I have one more, hopefully small, request.  Is there any way to implement the Member Color Link mod to the page?

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

[Cal]

A super easy way, in fact!  Add g.online_color to your SELECT string, and then call that in CSS for the member name cell.  So, like this:

Code: [Select]

SELECT m.real_name, m.id_member, g.group_name, g.online_color, GROUP_CONCAT(CASE WHEN t.value = "" THEN "N/A" ELSE t.value END ORDER BY t.variable SEPARATOR "</td><td class=\"windowbg\">") AS custom_fields, m.date_registered

And then

Code: [Select]

<td class="windowbg" style="color: ' . $row['online_color'] . ';"><a href="' . $scripturl . '?action=profile;u=' . $row['id_member'] . '" title="' . $txt['profile_of'] . ' ' . $row['real_name'] . '">' . $row['real_name'] . '</a></td>