Echo all members function WHERE value='' ?

I think this will work, but it is untested so watch for typos:

$sql = "
SELECT user_login
FROM wp_users
JOIN wp_mngl_custom_field_values ON (ID = user_id)
WHERE value = 'Winter'";
$logins = $wpdb->get_results($sql);
foreach ($logins as $login) {
   echo $login->user_login;
}

Hi vtxyzzy,

First of all, thanks a lot for your help. I wasn’t really aware of the JOIN possibility so made me a bit smarter already;)

I tested the script and watched for typos, but the echo gave me 0 results, still i am sure that i do have 2 users with that certain value. Maybe you know what’s the error?

Thanks a lot in advance!

not tested but try with this:
JOIN wp_mngl_custom_field_values ON (wp_users.ID = wp_mngl_custom_field_values.user_id)

Unless there are duplicate column names in the tables, the table name qualification is unnecessary.

I don’t see anything wrong based on the info we have so far.

@keesiemeijer and @vtxyzzy thanks alot, worked out!

Cheers;)

Sorry to bother you again; but what if i want a more simple function (at least i thought it would be but still cant work it out):

What i would like is the function to echo a certain variable where the value of another field is “Winter” from 1 and the same table. Tried something like this:

<?php
$sql = "
SELECT value WHERE field_id ='2'
FROM wp_mngl_custom_field_values
WHERE value = 'Winter' AND field_id ='1'";
$logins = $wpdb->get_results($sql);
foreach ($logins as $login) {
echo $login->value;
}
?>

Could you pls help me out 1 more time?
Thanks a lot in advance!

I don’t understand what you want. Can you explain further and give a couple of examples?

Oops, sorry about that.
What i want seems to be quite simple.

The mingle database structure is like this:

field_id     user_id     value
1            1           Name
2            1           season
3            1           age
1            2           Name
2            2           season
3            2           age

Now what i want is to echo someones name, with the season value ‘winter’ from the table: wp_mngl_custom_field_values

So if season is winter, echo name.

Hope thats more clear, and thanks a lot for yr time!

I understand now. I think this is what you want:

$sql = "
SELECT cfv1.value as Name
FROM wp_mngl_custom_field_values cfv1
JOIN wp_mngl_custom_field_values cfv2 ON (cfv2.user_id = cfv1.user_id)
WHERE cfv1.field_id = 1
AND cfv2.field_id = 2
AND cfv2.value = 'Winter'";
$rows = $wpdb->get_results($sql);
foreach ($rows as $row) {
   echo $row->Name;
}

Waa, thanks a lot again.. it WORKS!

Thanks a lot!;)

One more, final i promise, question. I use the mingle community plugin for my weblog. Now within that plugin there’s an option to show a directory (list) of all the members (usernames). Now what i want is to not only show the usernames, but also some other additional (user specific) info from another table.

Now i found the script that echo’s the usernames in mingle:

<?php

  $avatar_thumb_size = 64;

  if(is_array($profiles))
  {
    foreach ($profiles as $key => $profile)
    {
      $avatar_link = $profile->get_avatar($avatar_thumb_size);

      $full_name = $profile->screenname;

      if(!empty($search_query))
      {
        $full_name = preg_replace( "#({$search_query})#i", "<span class=\"mngl-search-match\">$1</span>", $full_name );
      }
?>

What i really would like is to also show information per user from the table: “wp_mngl_custom_field_values” and then echo the values where the field_id=’1′ and echo the value where the field_id=’2′.

Hope i made myself clear this time and that ur willing to help me out one more time;)

Thanks a lot again!
Kind regards,
Robbert

I don’t see the user_id in the code you posted. You will need that to get the custom field values.

You probably need to find where the $profiles array is created and modify the code there to get the values so you can display them in the code above.

Thanks for your reply. I understand what you mean. I found 2 bits of code which might be the part you mean.

$query = "SELECT ID FROM {$wpdb->users}";
          $user_ids = $wpdb->get_col($query,0);

          foreach ($user_ids as $user_id)
          {
            $profile = MnglUser::get_stored_profile_by_id($user_id);

and

$order_by  = ((!empty($order_by))?" ORDER BY {$order_by}":'');
    $limit_str = (($limit > 0)?" LIMIT {$offset},{$limit}":'');
    $query = "SELECT ID FROM {$wpdb->users} {$where}{$order_by}{$limit_str}";

    $user_ids = $wpdb->get_col($query,0);
    $profiles = array();

    if(empty($user_ids))
      return false;

    foreach ($user_ids as $user_id)
    {
      $curr_profile = MnglUser::get_stored_profile_by_id($user_id);

      if($curr_profile)
        $profiles[] = $curr_profile;
    }

    return $profiles;
  }

Is there any way you can help me further based upon the above info? Would really appreciate it!
Thanks a lot for yr time.

This is pretty complex and UNTESTED. I have to assume that $profile is an object and that I can add attributes to it.

You need to add code the the last snippet that you showed to get the additional fields and add them to $profile:

if(empty($user_ids))
      return false;

     // Get the additional fields for each user
     $sql = "
       SELECT cfv1.user_id, cfv1.value as cfv_name, cfv2.value as cfv_season
       FROM wp_mngl_custom_field_values cfv1
       JOIN wp_mngl_custom_field_values cfv2 ON (cfv2.user_id = cfv1.user_id)
       WHERE cfv1.field_id = 1
       AND cfv2.field_id = 2";
     $rows = $wpdb->get_results($sql);
     foreach ($rows as $row) {
        $cfv_by_id[$row->user_id] = $row;
     }

    foreach ($user_ids as $user_id)
    {
      $curr_profile = MnglUser::get_stored_profile_by_id($user_id);

      if($curr_profile)
         $curr_profile->cfv_name = $cfv_by_id[$user_id]->cfv_name;
         $curr_profile->cfv_season = $cfv_by_id[$user_id]->cfv_season;

        $profiles[] = $curr_profile;
    }

Then, where the usernames are echoed, echo each of the additional fields.

Good Luck!

Hello,
Thanks a lot again!
If I did understand you right I had to do the following things:

1: Changed my last snippet of the code in:

if(empty($user_ids))
      return false;

  // Get the additional fields for each user
     $sql = "
       SELECT cfv1.user_id, cfv1.value as cfv_name, cfv2.value as cfv_season
       FROM wp_mngl_custom_field_values cfv1
       JOIN wp_mngl_custom_field_values cfv2 ON (cfv2.user_id = cfv1.user_id)
       WHERE cfv1.field_id = 1
       AND cfv2.field_id = 2";
     $rows = $wpdb->get_results($sql);
     foreach ($rows as $row) {
        $cfv_by_id[$row->user_id] = $row;
     }

    foreach ($user_ids as $user_id)
    {
      $curr_profile = MnglUser::get_stored_profile_by_id($user_id);

 if($curr_profile)
         $curr_profile->cfv_name = $cfv_by_id[$user_id]->cfv_name;
         $curr_profile->cfv_season = $cfv_by_id[$user_id]->cfv_season;
$profiles[] = $curr_profile;

    }

    return $profiles;
  }

2: Echo the fields:

<?php 

      echo $curr_profile->cfv_name = $cfv_by_id[$user_id]->cfv_name;
	   echo $curr_profile->cfv_season = $cfv_by_id[$user_id]->cfv_season;
?>

Hope that is what you wanted me to do, in any case; i did that;)
Was this what you wanted me to do? I tried, but it ends up echo-ing nothing unfortunately.

Hope to hear from you, thanks again!