Custom Select Query by Authors information

Ok, so something strange is happening.

The only way I get some results is when in the AGE field I set up a default value that will be applied automatically to all users; for example ’55’. The same for the field CITY example ‘Madrid’.

now when the code matches the values Madrid and 55 like in the code below;

<?php
global $wpdb;
global $userid;

$querystr = "SELECT wp_users.ID, user_login, display_name, cimy_age.VALUE as age, cimy_city.VALUE as city
FROM wp_users
JOIN wp_cimy_uef_fields cimy_fields1 ON cimy_fields1.NAME = 'CITY'
JOIN wp_cimy_uef_fields cimy_fields2 ON cimy_fields2.NAME = 'AGE'
JOIN wp_cimy_uef_data cimy_age ON
   (cimy_age.USER_ID = wp_users.ID AND cimy_age.FIELD_ID = cimy_fields2.ID)
JOIN wp_cimy_uef_data cimy_city ON
   (cimy_city.USER_ID = wp_users.ID AND cimy_city.FIELD_ID = cimy_fields1.ID)
WHERE cimy_fields1.VALUE = 'Madrid'
   AND cimy_fields2.VALUE = '55'";

$authorset = $wpdb->get_results($querystr, OBJECT); 

?> 

<?php if ($authorset): ?>
<?php global $wpdb; ?>
<?php
echo '<ul>';
foreach ($authorset as $userid){
  $return = '';
  $user_id       = (int) $userid->ID;
  $user_data = get_userdata($user_id);
  $return .= "\t" . "<li>$user_data->first_name $user_data->last_name  $userid->age $user_id->city</li>" . "\n";

  print($return);
}
echo '</ul>';
?>

<?php endif; ?>

I don’t get still any results.
But from the moment I go manually change in a profile page the city or age, i get displayed the users name, surname, age (no city is displayed) of the modified profiles.

There is no result in any other way

What is wrong?

when I am saying putting default values to fields, I mean putting them in in the values fields on the Cimy User Extra Fields options page.

The code worked in my test site. There is no way to tell what is going on without an admin login to your site, and phpMyAdmin access to examine the tables in the database.

If you are willing to give me that access, email me at mac =at= mcdspot =dot= com.

ok, I sent you a mail

Thanks. I will look at it now.

Thank you vtxyzzy

Here is the solution on how to pull out user name, last name, and some cimy fields based on the cimy fields values:

<?php
global $wpdb;
global $userid;

$querystr = "SELECT $wpdb->users.ID,display_name,cimy_city.VALUE as city, cimy_age.VALUE as age
FROM $wpdb->users, {$wpdb->prefix}cimy_uef_data cimy_city, {$wpdb->prefix}cimy_uef_fields cimy_fields1,
   {$wpdb->prefix}cimy_uef_data cimy_age, {$wpdb->prefix}cimy_uef_fields cimy_fields2
WHERE $wpdb->users.ID = cimy_city.USER_ID
   AND $wpdb->users.ID = cimy_age.USER_ID
   AND cimy_city.FIELD_ID = cimy_fields1.ID
   AND cimy_fields1.NAME ='CITY'
   AND cimy_age.FIELD_ID = cimy_fields2.ID
   AND cimy_fields2.NAME ='AGE'
   AND cimy_city.VALUE = 'Madrid'
   AND cimy_age.VALUE = 55";

$authorset = $wpdb->get_results($querystr, OBJECT); 

if ($authorset):
   global $wpdb;
   echo '<ul>';
   foreach ($authorset as $userid){
      $user_id       = (int) $userid->ID;
      $userdata = get_userdata($user_id);
      $user_fullname = $userdata->last_name . ', ' . $userdata->first_name;
      $user_url = add_query_arg('author',$user_id, get_bloginfo('url'));
      $user_link = "<a href='$user_url' alt='Link To Author $user_id' >$user_fullname</a>";
      $return = "\t" . "<li>$user_link $userid->age $userid->city</li>" . "\n";

      print($return);
   }
   echo '</ul>';
endif; ?>

Glad it is working!