Custom Profile Field Mod

[rpalmer68]

Hi! I've already read all the postings in this thread and it didn't give any clue on how to display records in tabular format.& It only has info on grabbing data of one record in displaying it in the Profile page. What I want to do is display ALL Member records and grabbing data from both smf_members and smf_themes database tables. 

Later on, I will also include a Filter combo menu on the top of my Member Listing page so it can grab only those records with selected data.

I just need a sample PHP code using "while" loop and the corresponding MySQL query that grabs data from both smf_members and smf_themes database tables. If anyone can give me an actual sample or point me to the right direction, I would appreciate it.

I thought what B12 posted was what you wanted? 
My script generates a tabulated html page with the fields populated for each member of the forum (from themes and members tables), in my case to generate a  phone list for members to print out.

Here's my code based on B12's response and just tweaked a bit to handle blank fields and formatted how I needed it.

phonelist.php

Code Select Expand


<?
mysql_connect("localhost","<DATABASE LOGIN>","<PASSWORD>");
mysql_select_db("<DATABASE NAME>");

$select = "SELECT ID_MEMBER FROM smf_themes WHERE variable = 'CP_instrument' ORDER BY value";
$query = mysql_query($select)or die(mysql_error());
$t=getdate();
$today=date('jS M Y',$t[0]);
echo "<center><strong>MEMBERSHIP LIST<br>";
echo "Generated on the " . $today;
echo "</strong>";
echo"<br>";
echo "<table width=1200 border=1>";
echo "<tr>";
echo "<th width=200 scope=col>Instrument</th>";
echo "<th width=150 scope=col>Name</th>";
echo "<th width=120 scope=col>Home Phone</th>";
echo "<th width=120 scope=col>Work Phone</th>";
echo "<th width=120 scope=col>Mobile Phone</th>";
echo "<th width=300 scope=col>Email Address</th>";
echo "<th width=300 scope=col>Address</th>";
echo "</tr>";


echo "<br>";
while($list = mysql_fetch_object($query))
{
echo "<tr>";
$id = $list->ID_MEMBER;



//--- get the instrument from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_instrument'";
$res = mysql_query($sql);
$instrument = mysql_result($res, 0);



//--- get the homePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homePhone'";
$res = mysql_query($sql);
$homePhone = mysql_result($res, 0);
if ($homePhone ==""){
&nbsp; &nbsp; $homePhone = "&nbsp;";
}


//--- get the workPhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_workPhone'";
$res = mysql_query($sql);
$workPhone = mysql_result($res, 0);
if ($workPhone ==""){
&nbsp; &nbsp; $workPhone = "&nbsp;";
}

//--- get the mobilePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_mobilePhone'";
$res = mysql_query($sql);
$mobilePhone = mysql_result($res, 0);
if ($mobilePhone ==""){
&nbsp; &nbsp; $mobilePhone = "&nbsp;";
}

//--- get the homeAddress from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homeAddress'";
$res = mysql_query($sql);
$homeAddress = mysql_result($res, 0);
if ($homeAddress ==""){
&nbsp; &nbsp; $homeAddress = "&nbsp;";
}


//--- get the membername from the database
$sql = "SELECT realName FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$memberName = mysql_result($res, 0);

//--- get the email from the database
$sql = "SELECT emailAddress FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$emailAddress = mysql_result($res, 0);

//--- echo the results
echo&nbsp; "<td>$instrument</td><td>$memberName</td><td>$homePhone</td><td>$workPhone</td><td>$mobilePhone</td><td>$emailAddress</td><td>$homeAddress</td>" ;
echo "</tr>";
}
echo "</table>"
?>




Richard

[tsmalmbe]

nice function...

it should probably be placed in sources/subs.php rather than in a template...

Probably so. Feel free to grab and include somewhere. Or give me more "direct" instructions. Or perhaps make this a standard feature in 2.0?

[Kindred]

custom profile fields are included in 2.0, but are likely going to be handled slightly differently than this mod...

[brelwit]

I thought what B12 posted was what you wanted? 
My script generates a tabulated html page with the fields populated for each member of the forum (from themes and members tables), in my case to generate a  phone list for members to print out.

Here's my code based on B12's response and just tweaked a bit to handle blank fields and formatted how I needed it.

phonelist.php

Code Select Expand


<?
mysql_connect("localhost","<DATABASE LOGIN>","<PASSWORD>");
mysql_select_db("<DATABASE NAME>");

$select = "SELECT ID_MEMBER FROM smf_themes WHERE variable = 'CP_instrument' ORDER BY value";
$query = mysql_query($select)or die(mysql_error());
$t=getdate();
$today=date('jS M Y',$t[0]);
echo "<center><strong>MEMBERSHIP LIST<br>";
echo "Generated on the " . $today;
echo "</strong>";
echo"<br>";
echo "<table width=1200 border=1>";
echo "<tr>";
echo "<th width=200 scope=col>Instrument</th>";
echo "<th width=150 scope=col>Name</th>";
echo "<th width=120 scope=col>Home Phone</th>";
echo "<th width=120 scope=col>Work Phone</th>";
echo "<th width=120 scope=col>Mobile Phone</th>";
echo "<th width=300 scope=col>Email Address</th>";
echo "<th width=300 scope=col>Address</th>";
echo "</tr>";


echo "<br>";
while($list = mysql_fetch_object($query))
{
echo "<tr>";
$id = $list->ID_MEMBER;



//--- get the instrument from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_instrument'";
$res = mysql_query($sql);
$instrument = mysql_result($res, 0);



//--- get the homePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homePhone'";
$res = mysql_query($sql);
$homePhone = mysql_result($res, 0);
if ($homePhone ==""){
&nbsp; &nbsp; $homePhone = "&nbsp;";
}


//--- get the workPhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_workPhone'";
$res = mysql_query($sql);
$workPhone = mysql_result($res, 0);
if ($workPhone ==""){
&nbsp; &nbsp; $workPhone = "&nbsp;";
}

//--- get the mobilePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_mobilePhone'";
$res = mysql_query($sql);
$mobilePhone = mysql_result($res, 0);
if ($mobilePhone ==""){
&nbsp; &nbsp; $mobilePhone = "&nbsp;";
}

//--- get the homeAddress from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homeAddress'";
$res = mysql_query($sql);
$homeAddress = mysql_result($res, 0);
if ($homeAddress ==""){
&nbsp; &nbsp; $homeAddress = "&nbsp;";
}


//--- get the membername from the database
$sql = "SELECT realName FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$memberName = mysql_result($res, 0);

//--- get the email from the database
$sql = "SELECT emailAddress FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$emailAddress = mysql_result($res, 0);

//--- echo the results
echo&nbsp; "<td>$instrument</td><td>$memberName</td><td>$homePhone</td><td>$workPhone</td><td>$mobilePhone</td><td>$emailAddress</td><td>$homeAddress</td>" ;
echo "</tr>";
}
echo "</table>"
?>



Richard

Thanks for the help.    I saw your code, but when I noticed there are several separate MySQL statements I thought it might be too slow when querying the records.  How is it working?  How many members do your site currently has?

Is there any other solution that only uses one MySQL query for both smf_members and smf_themes database tables?

[sunlight]

Regarding: adding custom fields to user lists
i found an old winrules' reply to my message.
it was for a quite old version of smf i think 1.0.x
I have not much time right now.. anyone could test if this works ?
it worked with old smf:

Find:

Code:
      if (!loadMemberContext($member))
         continue;
Replace:

Code:
      if (!loadMemberContext($member))
         continue;

   global $context, $sourcedir, $user_profile;
   require_once($sourcedir . '/Profile.php');
   $context['user']['is_owner'] = 0;
   $user_profile[$member]['ID_THEME'] = 1;
   loadThemeOptions($member);
   $context['members'][$member]['options'] = isset($context ['member']['options']) ? $context['member']['options'] : array();

And then you can use $member['options']['field_name'] to display the field. Being able to search and sort is an entirely different thing however.

« Last Edit: June 12, 2006, 07:23:02 PM by winrules » 

[tsmalmbe]

custom profile fields are included in 2.0, but are likely going to be handled slightly differently than this mod...

Sure, doesn't reaelly matter. As long as the requirement to be able to easily use them globally from all templates with a nice convenience-method then all is well!

It is  a requirement, right?

[Kindred]

modifying templates for additional fields may always be somewhat manual...

[rpalmer68]

Thanks for the help.    I saw your code, but when I noticed there are several separate MySQL statements I thought it might be too slow when querying the records.  How is it working?  How many members do your site currently has?

I only have about 50 members so it performs fine for me.  Not sure how it would perform on a really large membership,  but I guess it also depends on how often people need to use it as to whether this is really an issue.

Richard

[brelwit]

I only have about 50 members so it performs fine for me.  Not sure how it would perform on a really large membership,  but I guess it also depends on how often people need to use it as to whether this is really an issue.

Richard

I might just go with your code if I can't think of any other solution.  But I will have to remove the "filter" feature for those related fields in the smf_themes table.    I think it's just not possible to have one mysql query for both "smf_members" and "smf_themes" database tables.  Well, at least I can still get to display all data in tabular format...

[Roxius]

Could someone please help me?

My SMF is 2.1.1, but it's not working, I can't see any change or look to edit - I have looked everywhere.

Do I have to edit any file?

Help!

[Mabba]

2.1.1 not 1.1.2?

[merosler]

Hi all,

I want to somehow allow my users to sign up for the newsletters sent via feedburner from within the registration page, however there doesn't seem to be a great way of doing this.

...I can't just add the text within the custom profile fields without having to select a checkbox, text, text field or pulldown to associate with this.  So I have decided to place the checkbox here, however if a user checks this, it doesn't really sign the users up for the newsletter.  ...They have to click the html text to the left of the check box which then opens up a feedburner pop up window for them.  Based on some of my user interactions, I don't think it's 100% apparent that they have to do this.

You can see how I set it up here:
http://went2networks.com/smf/index.php?action=register

Is there a way of integrating this feedburner sign up service in a way that I'm not seeing?

I have attached the feedburner html in case someone figures out a way to integrate the code into the custom profile mod tool..

thanks for your help!
matt

[tsmalmbe]

modifying templates for additional fields may always be somewhat manual...

Of course. But having convenience methods for accessing the stuff you need (including all the fileds in the profile) would be the requirement, right?

[Ryukai]

Hi! I've already read all the postings in this thread and it didn't give any clue on how to display records in tabular format.& It only has info on grabbing data of one record in displaying it in the Profile page. What I want to do is display ALL Member records and grabbing data from both smf_members and smf_themes database tables. 

Later on, I will also include a Filter combo menu on the top of my Member Listing page so it can grab only those records with selected data.

I just need a sample PHP code using "while" loop and the corresponding MySQL query that grabs data from both smf_members and smf_themes database tables. If anyone can give me an actual sample or point me to the right direction, I would appreciate it.

I thought what B12 posted was what you wanted? 
My script generates a tabulated html page with the fields populated for each member of the forum (from themes and members tables), in my case to generate a  phone list for members to print out.

Here's my code based on B12's response and just tweaked a bit to handle blank fields and formatted how I needed it.

phonelist.php

Code Select Expand


<?
mysql_connect("localhost","<DATABASE LOGIN>","<PASSWORD>");
mysql_select_db("<DATABASE NAME>");

$select = "SELECT ID_MEMBER FROM smf_themes WHERE variable = 'CP_instrument' ORDER BY value";
$query = mysql_query($select)or die(mysql_error());
$t=getdate();
$today=date('jS M Y',$t[0]);
echo "<center><strong>MEMBERSHIP LIST<br>";
echo "Generated on the " . $today;
echo "</strong>";
echo"<br>";
echo "<table width=1200 border=1>";
echo "<tr>";
echo "<th width=200 scope=col>Instrument</th>";
echo "<th width=150 scope=col>Name</th>";
echo "<th width=120 scope=col>Home Phone</th>";
echo "<th width=120 scope=col>Work Phone</th>";
echo "<th width=120 scope=col>Mobile Phone</th>";
echo "<th width=300 scope=col>Email Address</th>";
echo "<th width=300 scope=col>Address</th>";
echo "</tr>";


echo "<br>";
while($list = mysql_fetch_object($query))
{
echo "<tr>";
$id = $list->ID_MEMBER;



//--- get the instrument from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_instrument'";
$res = mysql_query($sql);
$instrument = mysql_result($res, 0);



//--- get the homePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homePhone'";
$res = mysql_query($sql);
$homePhone = mysql_result($res, 0);
if ($homePhone ==""){
&nbsp; &nbsp; $homePhone = "&nbsp;";
}


//--- get the workPhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_workPhone'";
$res = mysql_query($sql);
$workPhone = mysql_result($res, 0);
if ($workPhone ==""){
&nbsp; &nbsp; $workPhone = "&nbsp;";
}

//--- get the mobilePhone from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_mobilePhone'";
$res = mysql_query($sql);
$mobilePhone = mysql_result($res, 0);
if ($mobilePhone ==""){
&nbsp; &nbsp; $mobilePhone = "&nbsp;";
}

//--- get the homeAddress from the database
$sql = "SELECT value FROM smf_themes WHERE ID_MEMBER = " . $id . " AND variable = 'CP_homeAddress'";
$res = mysql_query($sql);
$homeAddress = mysql_result($res, 0);
if ($homeAddress ==""){
&nbsp; &nbsp; $homeAddress = "&nbsp;";
}


//--- get the membername from the database
$sql = "SELECT realName FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$memberName = mysql_result($res, 0);

//--- get the email from the database
$sql = "SELECT emailAddress FROM smf_members WHERE ID_MEMBER = " . $id;
$res = mysql_query($sql);
$emailAddress = mysql_result($res, 0);

//--- echo the results
echo&nbsp; "<td>$instrument</td><td>$memberName</td><td>$homePhone</td><td>$workPhone</td><td>$mobilePhone</td><td>$emailAddress</td><td>$homeAddress</td>" ;
echo "</tr>";
}
echo "</table>"
?>




Richard

Dude thank you very much ... this script solved my headache.

[blykmik]

Great mod... It installed with no errors and works in the PMs...

I do have a custom theme (Alienation) and made the manual modifications to "display.template.php" but that didn't seem to do the trick.  The mod works for the default template, of course, and in both the Admin and PM sections of my custom theme... Just ONLY not in the posts.  From reading this thread, I gathered that the display template was the relevant file to manually add/replace.

Is there another file I need to modify to get the custom fields to show the same way they do in the PMs?

Thanks

[brelwit]

Has anybody tested the Registration page when the Custom Profile Field mod is installed?  Registering does not seem to work on mine.  It says Database Error etc. after a user has registered.  But an activation e-mail is still sent to the registrant.

[SMdot™]

Hi all,

I want to somehow allow my users to sign up for the newsletters sent via feedburner from within the registration page, however there doesn't seem to be a great way of doing this.

...I can't just add the text within the custom profile fields without having to select a checkbox, text, text field or pulldown to associate with this.  So I have decided to place the checkbox here, however if a user checks this, it doesn't really sign the users up for the newsletter.  ...They have to click the html text to the left of the check box which then opens up a feedburner pop up window for them.  Based on some of my user interactions, I don't think it's 100% apparent that they have to do this.

You can see how I set it up here:
http://went2networks.com/smf/index.php?action=register

Is there a way of integrating this feedburner sign up service in a way that I'm not seeing?

I have attached the feedburner html in case someone figures out a way to integrate the code into the custom profile mod tool..

thanks for your help!
matt

Is this Dueling Tampons thing a Newsletter? If so use the News and Newsletters Option (index.php?action=news) in your SMF Administration.

Once on the news page click Newsletters (index.php?action=news;sa=mailingmembers) click Next
and then check the option to Send in HTML Format if you so choose.

[brelwit]

There seems to be two errors when a guest registers in my forum. I checked the related pages and found that the "last insert record id" which has the variable name of $from[id] is not being grabbed. So this results to a blank value and therefore an error appears in my SQL script. I am unsure if this is related to this Custom Profile Field Mod but I only discovered it now after installing the mod.

Here are the error in the FORUM ERROR LOG page:

ERROR #1:

Code Select Expand


Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' mem.pm_ignore_list)) AS ignored,
FIND_IN_SET(, mem.buddy_li
File: c:\easyphp\www\tabletopwars2007\bb\Sources\Subs-Post.php
Line: 773



ERROR #2:

Code Select Expand


8: Undefined index: pm_register_id
File: c:\easyphp\www\tabletopwars2007\bb\Sources\Subs-Members.php
Line: 871


Anybody knows a solution for this? Thanks!

[brelwit]

Nevermind.  I discovered the culprit.  It's the "PM ON REGISTRATION" module!!!  Arrrrggghh... after countless hours of searching.  hehe

[Muppet]

I have become very frustrated with this mod  itis just what I am looking for but no matter what I try I can not get it to work  Could it be that I am using the Tiny Portals mod and one of their themes? Although I have tried it with the core default theme as well.. Any help would be appreciated.