Get an array of meta_values for a user meta_key

You just need to add a DISTINCT to your SQL query, something like:

$cities = $wpdb->get_col("SELECT DISTINCT(meta_value) FROM $wpdb->usermeta WHERE meta_key = 'my_cities_meta_key'" );

Alternatively, if you want to do it with php for some reason (if you want to know that a city is listed twice before displaying only unique entries)

$cities = $wpdb->get_col("SELECT meta_value FROM $wpdb->usermeta WHERE meta_key = 'my_cities_meta_key'" );
$cities = array_keys(array_flip($cities));

If you want to filter based on user role using only SQL, something like this should work, though it’s a little hacky because WP stores user roles in a serialized array:

$cities = $wpdb->get_col("SELECT Distinct(c.meta_value) 
    FROM $wpdb->usermeta AS r 
    INNER JOIN $wpdb->usermeta AS c
      ON r.user_id = c.user_id
    WHERE r.meta_value LIKE '%subscriber%'
    AND c.meta_key = 'my_cities_meta_key'");

To add filters based on multiple meta keys you do more INNER JOIN, e.g. for your region metakey:

$cities = $wpdb->get_col("SELECT Distinct(c.meta_value) 
    FROM $wpdb->usermeta AS r 
    INNER JOIN $wpdb->usermeta AS c
      ON r.user_id = c.user_id
    INNER JOIN $wpdb->usermeta AS re
      ON r.user_id = re.user_id
    WHERE r.meta_value LIKE '%subscriber%'
    AND c.meta_key = 'my_cities_meta_key'
    AND re.meta_key = 'region'");

Related Posts: