Listing specific members in php artical

[chipw]

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:

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

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

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

Here  is what I changed it to:

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

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

[chipw]

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:

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

Code Select Expand


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

[JPDeni]

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.

[chipw]

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

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

[JPDeni]

Try this:

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

[chipw]

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.

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

[JPDeni]

Okay. Try this:

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


[chipw]

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

[JPDeni]

It must have to do with that query.

Change this:

Code Select Expand


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


to

Code Select Expand


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


[chipw]

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

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