Listing specific members in php artical

[chipw]

Quick note....

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

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

Code Select Expand

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

[chipw]

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:

Code Select Expand

WHERE (ID_MEMBER=" . $member_string . ")

To this:

Code Select Expand

WHERE ID_MEMBER = '$member_string'

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

[JPDeni]

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?

[chipw]

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

[JPDeni]

After

Code Select Expand


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


add

Code Select Expand


if(db_affected_rows() != 0)
{


and at the very end of the code add

Code Select Expand


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


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.

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.

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

[chipw]

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.

[chipw]

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.   

[JPDeni]

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.

[chipw]

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.

[JPDeni]

After

Code Select Expand


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

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


add

Code Select Expand


$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

Code Select Expand


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