List of users based on usermeta

Did you ever figure this out? I’m trying to basically the same thing. Can you post a full chunk of your code so I can see how you are returning this information on the screen, etc? Maybe I can help figure it out.

Thanks a bunch!
Ryan

Only tested briefly in PhpMyAdmin, but i think this will do the job..

$ids_of_presidents = $wpdb->get_col( "
	SELECT um2.user_id
	FROM {$wpdb->usermeta} um1
	LEFT JOIN {$wpdb->usermeta} um2
		ON um1.user_id = um2.user_id
	WHERE um1.meta_key = 'predisent'
	AND um1.meta_value = 'on'
	AND um2.meta_key = 'last_name'
	ORDER by um2.meta_value ASC"
);

Hope that helps…

Thanks for your help!

I used this code and it works fine:

SELECT um2.user_id, um2.meta_value, um3.meta_value
FROM $wpdb->usermeta um1
LEFT JOIN $wpdb->usermeta um2 ON um1.user_id = um2.user_id
LEFT JOIN $wpdb->usermeta um3 ON um1.user_id = um3.user_id
WHERE um1.meta_key = '$Role'
AND um1.meta_value = 'on'
AND um2.meta_key = 'last_name'
AND um3.meta_key = 'first_name'
ORDER BY um2.meta_value, um3.meta_value ASC
LIMIT 0 , 30

I’m having a few issues with this:

<?php
$query ="SELECT um1.meta_value, um2.user_id, um2.meta_value, um3.meta_value, um4.meta_value
FROM wp_usermeta um1
RIGHT JOIN wp_usermeta um2 ON um1.user_id = um2.user_id
RIGHT JOIN wp_usermeta um3 ON um1.user_id = um3.user_id
RIGHT JOIN wp_usermeta um4 ON um1.user_id = um4.user_id
WHERE um1.meta_key = 'cat'
AND um2.meta_key = 'last_name'
AND um3.meta_key = 'first_name'
AND um4.meta_key = 'sex'
ORDER BY um1.meta_value ASC, um4.meta_value ASC
LIMIT 0 , 30";
$output = $wpdb->get_results($query);

foreach($output as $rider) {
 echo (var_dump($rider));
 echo("<br />");
}
?>

As you can see in the SELECT I’m trying to get five columns out of the database but I’m only getting two, namely sex and user ID. The SQL works fine in PHPMyAdmin but when I try to get it into a web page it’s not working so well, so I’m suspecting it’s something with the way I’m using get_results.

The reason you get two columns back only is because you’re selecting the same column several times, give your columns aliases and you’ll get all of them back correctly, for example..

SELECT
   um1.meta_value AS meta1,
   um2.user_id,
   um2.meta_value AS meta2,
   um3.meta_value AS meta3,
   um4.meta_value AS meta4
...

Hope that helps. ??