TinyPortal

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...

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

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.

Quotecode 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.

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?

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.

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.


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);


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.


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>';


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.

:) 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


global $db_prefix, $scripturl;


and change


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


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

to


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.

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....

Quick note....

I found where there was no one in a specific county, I would get:

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND (variable = 'CP1' OR variable='CP3' OR variable='CP2' OR variable='CP' at line 3

I changed the double quotes to single quotes on the WHERE line below to fix the error.
$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__);

However, when using single quotes on the WHERE line, the script will not display business listing.  I don't receive any errors, but also don't get a listing unless I change the single quotes back to double quotes.  Then everything displays just fine.

I'm sorry I didn't check this stuff out sooner.  :(

Ok, just by playing around and looking at other snipets of code I've been checking out, I have come to this conclusion that appears to work.

I changed this:
WHERE (ID_MEMBER=" . $member_string . ")

To this:
WHERE ID_MEMBER = '$member_string'

And it seems to have fixed my issue.  Hopefully LOL.

Okay. :) If it works, that's all that matters. I would think that the parentheses should be there, but if it works, it works.

The best thing, of course, would be to be sure that the second query will have a return. Do you want code to check for that?

Sure, that would be great.

I was also going to ask if there was a simple way to only allow members from a specific group or groups to be displayed.

Also, apparently you do coding for smf and tp.  Do you have a donation page somewhere.  I would love to make you a donation for all your help with this very custom code..

After


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


add


if(db_affected_rows() != 0)
{


and at the very end of the code add


}
else
  echo 'There are no businesses in Benton County';


QuoteI was also going to ask if there was a simple way to only allow members from a specific group or groups to be displayed.

It would require another query. It would be a piece of cake if the custom profile fields mod just added additional fields to the members table. But because of the way they are stored in the themes table, it's more difficult.

Let's see. After getting the ID_MEMBER numbers, there would be a query for the members table, picking up the group and additonal groups for each number. Then another loop through that result, schlepping just the ID_MEMBERs for the right groups into another array and then using that array for the query to the themes table.

QuoteI would love to make you a donation for all your help with this very custom code..

You're very nice. I would be just pleased as punch if you would make a donation to a local children's charity in your area. If you give me money, then it seems too much like work.  ;)

You sure I can't offer ya something?  Cause I could really use the ability to stop the display unless they are a specific membergroup or groups.  The reason for this is so your everyday joe can't make up some bogus stuff or use this as a way of spamming...  I will be verifying all company's before setting them to what ever group.

And quite honestly, I already donate heavily to the Ronald McDonald House.  They were a HUGE help when my son was in the hospital with giving me and my wife a clean free place to stay and shower.   :)

Honestly, I'd rather not get anything, moneywise. I'm a strange old hippie-type. :)

The Ronald MacDonald House is a great charity. I know that they fill a vital need for the families of sick children. It would please me greatly to think that my little programming stuff was helping them out.

I'll work on that code for you tomorrow. I'm about ready to shut down the computer for the night.

Thank you very much.  And yes they do do a lot.  My kid was in Detroit Children's Hospital for 3 weeks.  RMH was a HUGE help.  I lived about 70 miles away.

After


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

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


add


$request = db_query("
    SELECT ID_MEMBER, ID_GROUP, additionalGroups
    FROM {$db_prefix}members
    WHERE ID_MEMBER=" . $member_string . "
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

// Do this all a second time, this time filtering out the ones we don't want

$members = array();

$group = 4; // Change this to the group you're interested in

while ($row = mysql_fetch_assoc($request)) {
  $add_groups = explode(',',$row['additionalGroups']);
  if ($row['ID_GROUP'] == $group || in_array($group,$add_groups))
    $members[] = $row['ID_MEMBER'];
}

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

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



Regarding the parentheses thing, it'll work if you just change that original query to


$request = db_query("
    SELECT ID_MEMBER, value, variable
    FROM {$db_prefix}themes
    WHERE ID_MEMBER=" . $member_string , __FILE__, __LINE__);


It may return more information than you need, but it won't matter.

Ok, starting over.

Put in original code you gave me and edited a couple CP statements to correct them.

Added two users to benton county and they show up fine.

Remove one and the other still shows up fine.

Remove last user and get this error:

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND (variable = 'CP1' OR variable='CP3' OR variable='CP2' OR variable='CP' at line 3
File: /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code
Line: 28

I put in the query code that you gave me in last post and still get this error:

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
File: /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code
Line: 27

So, I put original query back in and then make changes that I found and put it in.  Fixes problem when there are no users listed for that county.

Here are the two versions (We are still on your original code other than the one line)

Here is the original query:

$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__);

Here  is what I changed it to:

$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__);

Ok, now I add one user ID 3 to that county and he shows up just fine.  Then I add a second user ID 1 to that county and only user ID 1 shows up.  But, the table has been added for the other user.  Just no data.  I played around adding a couple more users and took users away and whoever had the lowest ID # was the one that would show up.

Doing this example, I did not add in any other code you supplied (like the link to profile, if/else code, or membergroup code).

I'm not sure why it is doing this..

Ok, played around some more.

I put in the if/else code and changed that one line that I found back to your original code.  This stopped the error.  Not that it is a fix for the problem, but it stops the error from showing up when there is no one listed for that county and when there is more than one user listed for that county they all display now.

So, I decided to put the membergroup code back in.

As long as there is someone in that group, they show up.

If there isn't anyone in that group, I get this error:

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND (variable='CP1' OR variable='CP3' OR variable='CP2' OR variable='CP5'' at line 3
File: /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code
Line: 56

Here is the block of code (I added in the line numbers so you can see what shows up as line 56 for me.)


52)  $request = db_query("
53)      SELECT ID_MEMBER, value, variable
54)      FROM {$db_prefix}themes
55)      WHERE (ID_MEMBER=" . $member_string . ")
56)     AND (variable='CP1' OR variable='CP3' OR variable='CP2' OR variable='CP5' OR variable='CP6')", __FILE__, __LINE__);

Not sure if this will help
php version - 5.2.6
mysql client version - 4.1.22

The point of the "if" statement is to not do any more queries if there aren't any people to be displayed. So put the latest code I gave you inside the "if" statement.

If you need me to look it over, please post the whole code you are actually using.

If you need more access to play with it, I'll PM you my access to the site.

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 = 'CP4'
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

if(db_affected_rows() != 0)
{

// 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);

//here is the membergroup code
$request = db_query("
    SELECT ID_MEMBER, ID_GROUP, additionalGroups
    FROM {$db_prefix}members
    WHERE ID_MEMBER=" . $member_string . "
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

// Do this all a second time, this time filtering out the ones we don't want

$members = array();

$group = 10; // Change this to the group you're interested in

while ($row = mysql_fetch_assoc($request)) {
  $add_groups = explode(',',$row['additionalGroups']);
  if ($row['ID_GROUP'] == $group || in_array($group,$add_groups))
    $members[] = $row['ID_MEMBER'];
}

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

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

//and that is the end of the membergroup code

// 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='CP2' OR variable='CP5' OR 57)variable='CP6')", __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]['CP2'] . '</td>';
  echo ' <td>' . $info[$member]['CP3'] . '</td>';
  echo ' <td>' . $info[$member]['CP5'] . '</td>';
  echo ' <td>' . $info[$member]['CP6'] . '</td>';  // Use the field for the phone here
  echo ' </tr>';
}
echo ' </table>';
echo '</div>';
}
else
  echo 'There are no businesses in Benton County';

Try this:


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 = 'CP4'
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

if(db_affected_rows() != 0)
{

// 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);

//here is the membergroup code
  $request = db_query("
      SELECT ID_MEMBER, ID_GROUP, additionalGroups
      FROM {$db_prefix}members
      WHERE ID_MEMBER=" . $member_string . "
      ORDER BY ID_MEMBER", __FILE__, __LINE__);
  if(db_affected_rows() != 0)
  {

// Do this all a second time, this time filtering out the ones we don't want

    $members = array();

    $group = 10; // Change this to the group you're interested in

    while ($row = mysql_fetch_assoc($request)) {
      $add_groups = explode(',',$row['additionalGroups']);
      if ($row['ID_GROUP'] == $group || in_array($group,$add_groups))
        $members[] = $row['ID_MEMBER'];
    }

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

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

//and that is the end of the membergroup code

// 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 . ", __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]['CP2'] . '</td>';
      echo ' <td>' . $info[$member]['CP3'] . '</td>';
      echo ' <td>' . $info[$member]['CP5'] . '</td>';
      echo ' <td>' . $info[$member]['CP6'] . '</td>';  // Use the field for the phone here
      echo ' </tr>';
    }
    echo ' </table>';
    echo '</div>';
  }
  else
    echo 'There are no businesses in Benton County';
}
else
  echo 'There are no businesses in Benton County';


I added a second "if/then" to test for appropriate businesses.

I would rather not go to your site if I can manage not to. :)

Sorry, I'm slow....  Been running the Oklahoma mountains and the aircard don't work very well there...

Getting this error whether or not there is someone to list.

Quote
Parse error: syntax error, unexpected '[', expecting ']' in /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code on line 62

Okay. :) Try this:


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 = 'CP4'
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

if(db_affected_rows() != 0)
{

// 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);

//here is the membergroup code
  $request = db_query("
      SELECT ID_MEMBER, ID_GROUP, additionalGroups
      FROM {$db_prefix}members
      WHERE ID_MEMBER=" . $member_string . "
      ORDER BY ID_MEMBER", __FILE__, __LINE__);
  if(db_affected_rows() != 0)
  {

// Do this all a second time, this time filtering out the ones we don't want

    $members = array();

    $group = 10; // Change this to the group you're interested in

    while ($row = mysql_fetch_assoc($request)) {
      $add_groups = explode(',',$row['additionalGroups']);
      if ($row['ID_GROUP'] == $group || in_array($group,$add_groups))
        $members[] = $row['ID_MEMBER'];
    }

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

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

//and that is the end of the membergroup code

// 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 . ", __FILE__, __LINE__);

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

    while ($row = mysql_fetch_assoc($request)) {
      $m = $row['ID_MEMBER'];
      $v = $row['variable'];
      $info[$m][$v] = $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]['CP2'] . '</td>';
      echo ' <td>' . $info[$member]['CP3'] . '</td>';
      echo ' <td>' . $info[$member]['CP5'] . '</td>';
      echo ' <td>' . $info[$member]['CP6'] . '</td>';  // Use the field for the phone here
      echo ' </tr>';
    }
    echo ' </table>';
    echo '</div>';
  }
  else
    echo 'There are no businesses in Benton County';
}
else
  echo 'There are no businesses in Benton County';


QuoteParse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code on line 62

I tried it both ways again with a user and shows this same error...

It must have to do with that query.

Change this:


    $request = db_query("
        SELECT ID_MEMBER, value, variable
        FROM {$db_prefix}themes
        WHERE ID_MEMBER=" . $member_string . ", __FILE__, __LINE__);


to


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


ok, that takes us back to working if there is someone to list.  But errors out if there isn't LOL

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY ID_MEMBER' at line 4
File: /home2/arbonds/public_html/Sources/Load.php(1740) : eval()'d code(1331) : eval()'d code
Line: 58

I'll see if I can add all this to my test site and work on it. Tomorrow.

ok, thank you very much.  gotta hit the road myself.  not making any money if the tires aren't turning.

I'm thinking there might not be a way to fix the issue.  However, I made another discovery.  If there are no members listed for a particular county, that county page does not error.  It's acts just fine.

I've added a couple more fields and revised the code a tiny bit by removing the second if/else statement.  I'm posting here so that you can see it with the new information and for safe keeping for me.

Thank you very much for all that you have done.  I can't express how much I really appreciate the help you have given me.


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 = 'CP4' OR variable = 'CP7' OR variable = 'CP8')
    ORDER BY ID_MEMBER", __FILE__, __LINE__);

if(db_affected_rows() > 0)
{ 

// 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);

//here is the membergroup code
  $request = db_query("
      SELECT ID_MEMBER, ID_GROUP, additionalGroups
      FROM {$db_prefix}members
      WHERE ID_MEMBER=" . $member_string . "
      ORDER BY ID_MEMBER", __FILE__, __LINE__);



// Do this all a second time, this time filtering out the ones we don't want

    $members = array();

    $group = 10; // Change this to the group you're interested in

    while ($row = mysql_fetch_assoc($request)) {
      $add_groups = explode(',',$row['additionalGroups']);
      if ($row['ID_GROUP'] == $group || in_array($group,$add_groups))
        $members[] = $row['ID_MEMBER'];
    }

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

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

//and that is the end of the membergroup code

// 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 . "
        ORDER BY ID_MEMBER", __FILE__, __LINE__);

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

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

// print out the table and the column headings
    echo '<div align="center">';
    echo ' <table border="1" width="90%" cellspacing="4" cellpadding="3">';
    echo ' <tr>';
    echo ' <td colspan="5"><b><font size="4"><center>Benton County</center></b></font></td>';
    echo ' </tr>';
    echo ' <tr>';
    echo ' <td width="25%"><b>Business Name</b></td>';
    echo ' <td width="25%"><b>Address</b></td>';
    echo ' <td width="20%"><b>City</b></td>';
    echo ' <td width="10%"><b>State</b></td>';
    echo ' <td width="20%"><b>Phone</b></td>';
    echo ' </tr>';

// print out the individual information

    foreach ($members as $member) {

      echo ' <tr>';
      echo ' <td><a href="' . $scripturl . '?action=profile;u=' . $member . '">' . $info[$member]['CP1'] . '</a></td>';
      echo ' <td>' . $info[$member]['CP2'] . '</td>';
      echo ' <td>' . $info[$member]['CP3'] . '</td>';
      echo ' <td>' . $info[$member]['CP5'] . '</td>';
      echo ' <td>' . $info[$member]['CP6'] . '</td>';  // Use the field for the phone here
      echo ' </tr>';
    }
    echo ' </table>';
    echo '</div>';
}
else
echo '<center><b>There are no bondsmen listed in Benton County</b></center>';


I'm glad that you figured it out. I couldn't get it.