Listing specific members in php artical

[chipw]

How would I list members according to specific criteria?

I have the custom profile mod and the Tiny Portal mod.  I would like to create a php artical that displays members along with specific information according to predefined criteria.

The long uncomplicated version:

I'm creating a site for company owners.
I installed SMF 1.1.6
I installed custom profile mod for SMF
I installed TinyPortal v1.0.5 beta 1

I added custom profile sections to collect:

• business name
• address
• county

I will create php articles for each county
I want to display the business name and address located in each county.

So basically, I need something that will search for the specific county then display the members, their business name, and their business address in the corresponding article.

Any help with this would be great...

[JPDeni]

I can give it a try, but since I don't have those fields, I can't test it. I would need to know what the names of the fields are. Also, how do you want them ordered? By business name or by member name?

You don't have to have a separate article for each country. You can have it list the countries which are in the database when it starts and then have the user click which one they want.

If there are any other details that you want, please speak up now.

------------------------------------------------------------------------------
Notes for myself:
Has country been selected?
No --
Query smf_themes for country (need field name)
SELECT DISTINCT value
WHERE variable LIKE [field name]
ORDER BY value
Print out countries and links -- Stop
Yes --
Query smf_themes for the business name, address and country (need field names)
SELECT ID_MEMBER, variable, value
WHERE variable LIKE [field name] OR [field name] OR [field name]
Loop through to find the ones in the right country. Schlep member number into an array
Loop through again, to associate member number with business name and address
Can I do just one query for usernames, using the array in a WHERE statement?
order the array on
print it out

[chipw]

Ok, first of all....  Thank you very much for taking the time to try this out....
Second, I apologize for the delay in replying.  I'm an over the road truck driver, so getting on the internet is a bit harder for me.

Now, please bare with me as I try to wrap my brain around what you need...
Also, just for clarification, it is "county" not "country"

These are inserted into the "smf_settings" table.

There are a gazillion variables and corresponding values.  Below is the portion of the data base you are requesting.

code removed to shorten post.

Here is a link to the mod being used:
http://custom.simplemachines.org/mods/index.php?mod=319

Again, I can't thank you enough for this.

[JPDeni]

Forgive my delay in responding. I have had a really nasty flu and haven't been able to write my own name, much less code.

I still don't know what the names of your fields are. I can't figure out that MySQL dump.

When you go to the "Custom Profile Fields" page in your admin area, what are the names of the fields that are listed? Do they have actual names or are they just "CP1" "CP2" etc?

[chipw]

I just did some searching around my database and think I found what you are actually looking for....

Yes, the fields are listed as
Variable   |   Value
CP1               [business name]
CP3               [address]
CP4               [city]
CP2               [county]
CP5                [state]

Yes, they are in that order so that they show up in the users profile correctly.

That output example was taken from the smf_themes database.  These are listed by ID_MEMEBER

For instance, my profile
ID_MEMBER      |   ID_THEME   |    variable   |    value
1                                  1                     CP1                 Business Advances
1                                  1                     CP3                0000 Anystreet
1                                   1                     CP4               Town Name
1                                   1                    CP2                 County Name
1                                   1                    CP5                 State Name

I hope this helps...

This script will be used in an artical.  If I haven't mentioned that yet.

[chipw]

Can anyone help with this....  I have found this code and edited to pull my data.  But it isn't displaying.

It seems to be connecting just fine and I'm not getting any errors...  For variable and value columns, see above post.

Code Select Expand


global $db_prefix, $settings, $context;

$request = db_query("
    SELECT ID_MEMBER, variable, value
    FROM {$db_prefix}themes
    WHERE value = 'Benton County'
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

echo '<div style="text-align:center;text-decoration:underline;"><h3>Benton County</h3></div>';

while ($row = mysql_fetch_assoc($request)) {
echo '<div align="center">';
echo ' <table border="0" width="100%" cellspacing="4" cellpadding="3">';
echo ' <tr>';
echo ' <td colspan="5"> </td>';
echo ' </tr>';
echo ' <tr>';
echo ' <td width="36%">Business Name</td>';
echo ' <td width="20%">Address</td>';
echo ' <td width="11%">City</td>';
echo ' <td width="12%">State</td>';
echo ' <td width="16%">Phone</td>';
echo ' </tr>';
echo ' <tr>';
echo ' <td width="36%">' . $row['CP1'] . '</td>';
echo ' <td width="20%"></td>';
echo ' <td width="11%"></td>';
echo ' <td width="12%"></td>';
echo ' <td width="16%"></td>';
echo ' </tr>';
echo ' </table>';
echo '</div>';
}

mysql_free_result($request);


[JPDeni]

Sorry I've been sort of out of it. Health stuff hasn't been real good lately.

Your query won't give you what you want, because of the way that custom profile fields are stored.  It's not as simple as you might think it should be. You need to do a couple of queries as I laid out in my first post.

The code below is completely untested, but it is the direction that is best to go in.

Code Select Expand


global $db_prefix;

// Find out who has businesses in Benton County

$request = db_query("
    SELECT ID_MEMBER
    FROM {$db_prefix}themes
    WHERE value = 'Benton County'
    AND variable = 'CP2'
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

// create an array and to use that as part of a second query.

while ($row = mysql_fetch_assoc($request))
  $members[] = $row['ID_MEMBER'];

// make it all into one bit of text that we can put into a query.

$member_string = implode(" OR ID_MEMBER=",$members);

// Create a query to get all the information for those people
// The field for the phone would have to be included as well

$request = db_query("
    SELECT ID_MEMBER, value, variable
    FROM {$db_prefix}themes
    WHERE (ID_MEMBER=" . $member_string . ")
    AND (variable = 'CP1' OR variable='CP3' OR variable='CP4' OR variable='CP5')", __FILE__, __LINE__);

// put this into an array that you can print out

while ($row = mysql_fetch_assoc($request))
  $info[$row['ID_MEMBER']][$row['variable']] = $row['value'];
mysql_free_result($request);

// print out the table and the column headings
echo '<div align="center">';
echo ' <table border="0" width="100%" cellspacing="4" cellpadding="3">';
echo ' <tr>';
echo ' <td colspan="5"> </td>';
echo ' </tr>';
echo ' <tr>';
echo ' <td width="36%">Business Name</td>';
echo ' <td width="20%">Address</td>';
echo ' <td width="11%">City</td>';
echo ' <td width="12%">State</td>';
echo ' <td width="16%">Phone</td>';
echo ' </tr>';

// print out the individual information

foreach ($members as $member) {

  echo ' <tr>';
  echo ' <td>' . $info[$member]['CP1'] . '</td>';
  echo ' <td>' . $info[$member]['CP3'] . '</td>';
  echo ' <td>' . $info[$member]['CP4'] . '</td>';
  echo ' <td>' . $info[$member]['CP5'] . '</td>';
  echo ' <td>' . $info[$member]['CP1'] . '</td>';  // Use the field for the phone here
  echo ' </tr>';
}
echo ' </table>';
echo '</div>';


[chipw]

works perfect.  I had to edit a couple things to include the new phone number field and change a couple CP numbers, but PERFECT....  Now to clean up the output formatting...

I can't thank you enough for this...  You are a life saver...

If I can, can I ask one more hopefully quicky question.  What would I need to add to link the business name to their profile.  If it's too much don't worry, it's not a priority.  Just a wish.

Not sure how to mark this as solved.  But it is solved and again, I can't thank you enough for this.  Hope you feel better soon.

[JPDeni]

I'm glad it worked -- and that you were able to tweak it to suit what you needed.

In order to link from the business name to the profile change the first line to

Code Select Expand


global $db_prefix, $scripturl;


and change

Code Select Expand


echo ' <td>' . $info[$member]['CP1'] . '</td>';


(The one with the business name, in case I got the CP number wrong)

to

Code Select Expand


echo ' <td><a href="' . $scripturl . '?action=profile;u=' . $member . '">' . $info[$member]['CP1'] . '</a></td>';


I'll mark it solved.

Thanks for your good health wishes. For a while there, I thought I had the plague. Turned out to be the flu.

[chipw]

Perfect.... 

Glad to hear it's just the flu....  I had the bone aching feverish chills the other day, so I know the feeling.... 

Again, thank you so much....