Custom Select Query by Authors information

The CIMY data you want is kept in two tables: $wpdb->cimy_uef_fields and $wpdb->cimy_uef_data.

The query below shows how to select the age and city values for each user from these tables:

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

Thank you, I tried something now, but i think I am still far from the answer.

I have tried now to display the names of the users that have the same city and age values, how should the syntax look like for this?

I have tried something like;

<?php
global $wpdb;
global $userid;

$querystr = "
SELECT 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 = 'Madrid'
JOIN wp_cimy_uef_fields cimy_fields2 ON cimy_fields2.NAME = '30'
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)
"; 

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

?>  

<?php if ($authorset): ?>
<?php global $wpdb; ?>
<?php foreach ($authorset as $userid){
	$user_id       = (int) $userid->ID;
	$display_name  = ($userid->display_name);
	$return .= "\t" . '<li>'. $display_name .'</li>' . "\n";

	print($return);
}
?>
<?php endforeach; ?>
<?php endif; ?>

What is wrong with it? How to show the users surnames and names

Not quite sure what you want, but you cannot use the actual values as keys in the _cimy_uef_fields table. You must use the Label for the value as the NAME, and the ‘VALUE’ column to select the actual value. So, the Label for the city column is ‘CITY’ and its ‘VALUE’ would be ‘Madrid’ (NAME = ‘CITY’, VALUE=’Madrid’).

$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 = 30";

Once you get the user ID’s from this query, you can get the other cimy fields with code like this:

<?php foreach ($authorset as $userid){
   $user_id       = (int) $userid->ID;
   $surname  = get_cimyFieldValue($userid, 'SURNAME');
   $return .= "\t" . '<li>'. $surname .'</li>
' . "\n";

   print($return);
}

What I have done is adding some extra information on the users profile page using “Cimy User Extra Fields” plugin.
I added additional informations for all users about their town name and age.
Now what I need is to list on a page all the user names that live in the same city and have the same age.

Now i composed this

<?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 = 'LABORATORIJ'
JOIN wp_cimy_uef_fields cimy_fields2 ON cimy_fields2.NAME = 'TITULA'
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 = 'ATS'
   AND cimy_fields2.VALUE = dr.sc.";

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

?> 

<?php if ($authorset): ?>
<?php global $wpdb; ?>
<?php foreach ($authorset as $userid){
   $user_id       = (int) $userid->ID;
   $surname  = get_cimyFieldValue($userid, 'SURNAME');
   $return .= "\t" . '<li>'. $surname .'</li>
' . "\n";

   print($return);
}
?>
<?php endforeach; ?>
<?php endif; ?>

It shows some syntax errors around the line containing “print($return);”

Do you think this is the correct code for what i need?

I need to know the cimy field names for all the fields that you want to select and display.

For example, what is the cimy field name for the field that contains the city name? Is it ‘CITY’, or something else?

I can’t correct your code until I know all the cimy field names.

And, please explain what is in the fields ‘LABORATORIJ’, and ‘TITULA’
.

Pardon me,
I pasted the code containing wrong fields, LABORATORIJ and TITULA.
Instead of them I had to use CITY and AGE as I corrected 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 = 30";

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

?> 

<?php if ($authorset): ?>
<?php global $wpdb; ?>
<?php foreach ($authorset as $userid){
   $user_id       = (int) $userid->ID;
   $surname  = get_cimyFieldValue($userid, 'SURNAME');
   $return .= "\t" . '<li>'. $surname .'</li>
' . "\n";

   print($return);
}
?>
<?php endforeach; ?>
<?php endif; ?>

So the exact cimy fields are: CITY and AGE

Try removing this line:

<?php endforeach; ?>

Yes the cimy field name for the field that contains the city name is CITY, and for the age is AGE.

In the example you didn’t undarstend I used some other existing cimy fields, to get the list of users based on the laboratory name (cimy field LABORATORIJ) and college title (cimy field TITULA)

If I made too much confusion you can show me an example using some fake cimy fields names, I will undarstend, and then insert some mine.

i removed
<?php endforeach; ?> and there is no rerror anymore, but there is also nothing showing on the page. The cimy field names and values are correct.
Can it be maybe something wrong with the second part;

<?php if ($authorset): ?>
<?php global $wpdb; ?>
<?php foreach ($authorset as $userid){
   $user_id       = (int) $userid->ID;
   $surname  = get_cimyFieldValue($userid, 'SURNAME');
   $return .= "\t" . '<li>'. $surname .'</li>
' . "\n";

   print($return);
}
?>
<?php endif; ?>

Thank you for helping

Is the name of the cimy field for the surname actually ‘SURNAME’?

As I said before, I need to know all the cimy field names.

Ow sorry, for surname I mean the value that stays in the “Last name” field in the Profiles page, so it is not under cimy.

So I have under cimy fields: CITY, AGE
From the original wordpress profile page: name, surname.

The final print if user exists would show the users name, surname and next to it his city and age.

Replace this:

<?php foreach ($authorset as $userid){
   $user_id       = (int) $userid->ID;
   $surname  = get_cimyFieldValue($userid, 'SURNAME');
   $return .= "\t" . '<li>'. $surname .'</li>
' . "\n";

   print($return);
}
?>

with this:

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

No results, blank page, can you show me a simple example then, only how to show all the users having the same cimy field, let’s say HEIGHT is 190.

Just to see if it gets correctly the cimy data

First, lets try some debugging. Add a line to dump out $authorset and see what it shows:

$authorset = $wpdb->get_results($querystr, OBJECT);
print_r('<p>AUTHORSET: ');print_r($authorset);print_r('</p>');

Can you post a link to your site so I can see the output? If not, please copy and paste it here.

No, sorry, now it worked at the end, I had to change the order of the cimy fields of CITY and AGE to position #1 and #2

It just shows some wrong results.

I will let you know when I find out what is wrong.