Retrieving Custom Profile Field Data

[virus52]

Hay guys, first post so appologies in advance if this is a n00by question lol

Ok, long story short, I want to know how I would go about retrieving data stored in a Custom Profile Field for all members.

I'm using the built in custom fields in SMF 2.0, which has made things difficult as all the info I can find on doing this relates to some mod for 1.x.  One of the things that has really confused me is how the custom field data is linked to a given member, as looking at the MySQL database, I can see the custom fields table, I can see the members table, but there seems to be nothing linking the two together.  Some stuff I read says its stored under the themes table, but that seems to be again related to the 1.x mod and not the built in one in 2.0 (in any case I cant see anything there so I guess not? :S ).

To put it all in context, I'm creating a custom PHP Box for SimplePortal which will produce class statistics for members of our guild (for the MMORPG Aion, in case youre curious ).  Ive done all the graphical work already, and I already have custom fields set up so users can select their class in-game.  All I need now is to grab that data, tally it, then put the results into the PHP vars I have to make some pretty bar graphs.  I've done PHP and MySQL stuff directly before, but I understand there are SMF functions which do alot of the nasty MySQL queries for me already and it would be nice for this module to be portable and not require the admin to put in any database connection data if I can help it (security risks and all that ) so if theres a built-in way of doing this thats nice, safe and portable, I'm all ears

So again - I'm looking to retrieve custom profile field data from all users on SMF 2.0 using built-in functions as far as is humanly possible.  I know PHP and MySQL well enough, but am fairly new to SMF.  Any help is appreciated ty guys and gals

[Gargoyle]

I ran across this issue in the 1.1 line... I know this probably isn't what you want to hear but I had to manually re-write the whole mod and make the "custom fields" just like all the other ones in the member table.

To date I have added 7 that all use the members table instead of the themes table and it has worked out great. I can pull the info and display it anyway I want now.

I never did find a way to pull the data like your describing with something similar to the "custom fields" mod.. I was using code unknown posted way back... same concept.

[Arantor]

It's done exactly the same in 2.0; it's stored against the member in the themes table.

Just grab the content with a single query.

Code Select Expand

$query = $smcFunc['db_query']('', '
    SELECT variable, value FROM {db_prefix}themes WHERE id_member = {int:member} AND id_theme = 1',
    array(
        'member' => $context['user']['id'],
    )
);

There is a function as part of Profile.php that loads it all though I can't remember what it is offhand (and it also grabs the HTML to use to edit said fields)

[virus52]

It's done exactly the same in 2.0; it's stored against the member in the themes table.

Just grab the content with a single query.

Code Select Expand

$query = $smcFunc['db_query']('', '
    SELECT variable, value FROM {db_prefix}themes WHERE id_member = {int:member} AND id_theme = 1',
    array(
        'member' => $context['user']['id'],
    )
);

There is a function as part of Profile.php that loads it all though I can't remember what it is offhand (and it also grabs the HTML to use to edit said fields)

Holy ******ing ******, I'm a completly blind ******ing dumbass who doesnt deserve to be called a software engineer!  I took a closer look after reading what you said there, and I completly missed the "next page" button in PHP MyAdmin on the themes table!  there it is, on the second page, right at the bottom, thus why I didnt see it!

Thank you so much guys.  I knew I was probably being a noob lol   If I have any more /facepalm-worthy questions I'll post em in this thread to save clogging your forums.  Otherwise ty for the help

EDIT:  Actually while I'm at it - is there a "Total Members" variable somewhere around or is it a case of while-looping through the members table till I get a null?  Would be kinda handy to know so I dont go bloating the code any more than I need to lol

[Gargoyle]

It's done exactly the same in 2.0; it's stored against the member in the themes table.

Just grab the content with a single query.

Code Select Expand

$query = $smcFunc['db_query']('', '
    SELECT variable, value FROM {db_prefix}themes WHERE id_member = {int:member} AND id_theme = 1',
    array(
        'member' => $context['user']['id'],
    )
);

There is a function as part of Profile.php that loads it all though I can't remember what it is offhand (and it also grabs the HTML to use to edit said fields)

Very nice... I did not know that.. 2.0 is pretty sweet then huh ?

[Arantor]

Total members is available in $modSettings['totalMembers'], it's cached in the settings table, which is auto loaded on SMF start.

[Gargoyle]

LOL I miss understood....

Sorry....

[virus52]

Total members is available in $modSettings['totalMembers'], it's cached in the settings table, which is auto loaded on SMF start.

epicness -licksArantor in teh face and offers cookies and interwebz- 

[Arantor]

Anything else I can do for you on this?

[amuir]

I, too, was going nuts looking for this data, and made the same mistake of not going to page to of the themes table.  But, now that I've fund it, I'm discovering that my rookie PHP coding skills are not up to the task of doing what I need to do...

Basically, the code I'm trying to generate is a simplified memberlist, displaying 4 fields (from the custom profile fields) for each user in a table.  Oh, and I think the call you guys were looking for earlier to retrieve the Custom Profile Field data is loadCustomFields.

I've got my code working so that it loops through each member, and builds the table, but the data cells are all blank because I can't seem to figure out how to parse each user's custom profile fields.

Can anybody help me out here?

Here's what I have so far:

Code Select Expand


while ($row = mysql_fetch_array($WotPlist))
{
$WotP_memlisted = $row["id_member"];
$WotP_memnam = $row["member_name"];
loadCustomFields($WotP_memlisted.'forumProfile');
foreach ($context['custom_fields'] as $field)
{
$IGN     = $field['cust_ign'];
$coords1 = $field['cust_coords1'];
$coords2 = $field['cust_coords2'];
$resourc = $field['cust_resourc'];
}

$output = "<TR><TD>" . $WotP_memnam . "</TD><TD>" . $IGN . "</TD><TD>" . $coords1 . "</TD><TD>" . $coords2 . "</TD><TD>" . $resourc . "</TD></TR>";
echo $output;


I know that the problem is in the foreach loop, but I haven't a clue what needs to be changed. Please help...

[Arantor]

So, what version of SMF are you using, first up?

Note that there will be better ways of doing it than that, which might require more code but will likely be faster.

[amuir]

Haha, yeah, well, no doubt there are better ways, and I'm certainly open to that. As I said, I'm a rookie when it comes to PHP coding, so any and all advice is most welcome!

I'm using SMF 2.0 RC2.

[Arantor]

And what are the fields called that you're trying to get?

Is it for all members, some members? Where is the code you posted earlier supposed to go?

[amuir]

And what are the fields called that you're trying to get?

The fields (as referenced in the code block I posted earlier) are named IGN, Coords1, Coords2, and Resource, though of course the actual fields in the DB are called cust_ign, cust_coords1, cust_coords2, and cust_resourc.

Is it for all members, some members?

Most members. I suppose it would be a good idea if I restricted the results to a particular membergroup, which should be easy enough.

Where is the code you posted earlier supposed to go?

The code I posted earlier is part of a separate PHP script - not in the forum code at all.  At the start of my script I call SSI and Profile to define the necessary functions.

And while I'm in here asking questions, do you have any idea why sometimes my script might say I'm not logged in when I am?  Here's the relevant code:

Code Select Expand


if (!$context['user']['is_guest'])
{
# OK, we know they're registered and logged in, so we can display stuff...
include "inc_v2_camelotdb.php";
include "inc_v2_navi.php";
echo ('<br />');

$myid = $context['user']['id'];
$user = $user_info['username'];

//You can show other stuff here.  Like ssi_welcome().  That will show a welcome message like:
//Hey, username, you have 552 messages, 0 are new.
ssi_welcome();
echo ('<br />');
ssi_latestMember();
echo ('<br />Online: ');
ssi_logOnline();
echo ('<br />News: ');
ssi_news();
echo ('<br />');
echo "<br />\n";

// Other code stripped out here

}
else
{
echo ("You must <a href=\"/camelot/forum/\">log in</a> (or you are not authorized) to access this page!<br />\n");
}

I'm getting the "You must log in (or you are not authorized) to access this page!" message even though I am logged in.

[Arantor]

I don't know about your last point.

So, how do you want to restrict it? Might as well build that in as we go.

[amuir]

Restrict it to members in the "validated" membergroup.

[Arantor]

So, what group id is that?

[amuir]

Sorry, group number 9.

[Arantor]

And you want it to get a list of all the members in group 9, with those 4 fields attached? Any other information you want it to display?

[amuir]

Nope, just IGN, Coords1, Coords2, and Resource.

[Arantor]

What about username/display name?

[amuir]

My first round did include the member name, and it wouldn't *hurt* to have it in there, but the IGN will be the primary way we'll be identifying members.

Edit: Thinking about it again, I'd probably prefer to have it in there, even if it's something I end up stripping out later. For now, it lets me confirm that everything is matched up correctly.

[amuir]

I've just added another Custom Profile Field to the forum which I may want included later on, but I should be able to figure out how to do that once you can help me with this building block. Thanks!

[amuir]

Arantor seems to have moved on to other things. Can anybody else help me with this?

[Arantor]

I haven't, I've just been very busy, and formulating the queries for this isn't exactly easy. I've been trying to figure out whether to run with one megaquery to get everything or break it down into separate queries.

And where do you want all this to be, in a page in the forum, or in a separate page based on SSI.php?

[amuir]

OK, thanks. In a way, I'm glad to hear you say it's not as easy as I thought it would be. Makes me feel at least a *little* less like a noob.

This will not be part of the forum code. This is for a separate script which is using SSI.

Thanks again for your continued assistance!

[Arantor]

And is there any permission control required?

[amuir]

The script already successfully checks to make sure the user is logged in and a member of the "validated" member group.  Is that what you meant by permission control?

[Arantor]

Yeah, I didn't know if you had that already or not, or whether you wanted to set up an actual permission to trigger it.

I'll see if I can find some time tonight to work on this some more.

[amuir]

Nope, I think the only piece I'm missing is how to retrieve the various Custom Profile fields for each user as I loop through the memberlist and stick them into variables.  I'm already looping through the memberlist fine, and building the table, it's just that my cells in the table are all blank (except for the membername).

I'll keep checking back. Thanks!

[Arantor]

How are you getting the memberlist, exactly?

[amuir]

Probably not the "best" way to do it, but here's what I have so far and it works (except for the missing Custom Profile fields)...

Code Select Expand


$SQL = " SELECT * FROM koc_members ";
$SQL .= " WHERE 1 ";

# execute SQL statement
$WotPlist = mysql_query($SQL);

# check for errors
if (!$WotPlist) { echo( mysql_error()); }
else
{
# display results
while ($row = mysql_fetch_array($WotPlist))
{
$WotP_memlisted = $row["id_member"];
$WotP_memnam = $row["member_name"];
loadCustomFields($WotP_memlisted.'forumProfile');
foreach ($context['custom_fields'] as $field)
{
$IGN     = $field['cust_ign'];
$coords1 = $field['cust_coords1'];
$coords2 = $field['cust_coords2'];
$resourc = $field['cust_resourc'];
}

$output = "<TR><TD>" . $WotP_memnam . "</TD><TD>" . $IGN . "</TD><TD>" . $coords1 . "</TD><TD>" . $coords2 . "</TD><TD>" . $resourc . "</TD></TR>";
echo $output;
}

}


[Arantor]

That gets every user. I thought you wanted it limited to a given membergroup?

[amuir]

Yes, for now it does, but it's no big deal to add another clause to the WHERE statement (or exclude later with a conditional). Just haven't changed anything since I was waiting to hear back from you on the Custom Profile Fields before I go mucking in it again.

[Arantor]

It's kind of important I know whether you plan to change it though. The most efficient way will depend greatly on it.

[amuir]

OK, may as well have it only return members who are in the "Validated" membergroup (id=9).

The only other future change I foresee is that I may need to add additional columns to the table (meaning additional Custom Profile Fields) if more data becomes relevant. But there is currently nothing else I know of right now.

[Arantor]

OK, I think that all makes sense, I'll try and work on that today if I get time - no promises though.

[amuir]

Merry Christmas and Happy New Year!

Just checking in to see how things are going.  Anything else I can do to make it easier?

[Arantor]

Sorry, just been so busy with other stuff over the last couple of weeks I just haven't had time to sit and poke at this

[amuir]

I know you're busy, but maybe you could just give me a hint on what I'm doing wrong?  I'd like to get moving with this project, even if I have to stumble my way through it.  Since it's external to SMF, it won't matter too much if it's not the most efficient way of doing it, and I could easily clean up the code later.  Thanks!

[Arantor]

Well, first up, if you're inside SMF, should be using $smcFunc in 2.0, and never using mysql_fetch_array.

It would look something like the untested code below.

Code Select Expand

$list_of_fields = array('cust_ign', 'cust_coords1', 'cust_coords2', 'cust_resourc');

$data = array();

$query = $smcFunc['db_query']('', '
SELECT id_member, member_name FROM {db_prefix}members');

while ($row = $smcFunc['db_fetch_assoc']($query))
{
$data[$row['id_member']] = $row;
foreach ($list_of_fields as $field) // set up blanks for the fields we want in case they're empty
$data[$field] = '';
}

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

$query = $smcFunc['db_query']('', '
SELECT id_member, variable, value FROM {db_prefix}themes
WHERE variable IN ({array_string:fields}) AND id_theme = 1
', array(
'fields' => $list_of_fields,
)
);

while ($row = $smcFunc['db_fetch_assoc']($query))
{
$data[$row['id_member']][$row['variable']] = $row['value'];
}

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

foreach ($data as $row)
{
echo '
<tr>
<td>', $row['member_name'], '</td>';

foreach ($list_of_fields as $field)
echo '
<td>', $row[$field], '</td>';

echo '
</tr>';
}

[amuir]

Many thanks, Arantor!  I haven't done much with SMF, so I wasn't aware of many of the functions available.  I will look more into $smfFunc as I develop more SSI-based scripts.  The code you posted below worked without error, and I was able to tweak it the little bit needed to restrict it to the Validated membergroup. My members are very happy with the results, and I couldn't have done it without your assistance. Kudos!

[Arantor]

Awesome to hear