1

I need to get an array of all the meta_values for a user meta_key. I found this code:

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

which results in this:

array(3) { [0]=> string(8) "Chicago" [1]=> string(8) "New York" [2]=> string(8) "New York" }

This is not exactly what I need. See how New York value is listed twice because there are two users located in New York? I need an array that lists each unique meta_value once. Like this:

$cities = array(New York, Chicago, Los Angeles);

How do I achieve that?

1

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'");
  • Ah ha! I didn't know about DISTINCT. That solved my problem. Thank you! – LBF Apr 14 '15 at 18:12
  • Actually, do you know how I could revise the query to add an additional qualifier that says the user "role" must be "subscriber"? So basically, only pull values from the meta_key "my_cities_meta_key" if the users who have this key/value are subscribers. Does that make sense? – LBF Apr 14 '15 at 18:31
  • I've added it to the response. Let me know if you need clarification on the anatomy of the query. – TheGentleman Apr 14 '15 at 18:49
  • Thank you! That did the trick. I think I understand, though I'm not sure I totally getting what's happening here: INNER JOIN $wpdb->usermeta AS c ON r.user_id Would you mind explaining that in non-sql speak? – LBF Apr 14 '15 at 19:34
  • Since we are interested in 2 values (role/city) that are both in the usermeta table we have to join that table to itself but lookup a different meta key. I called the city lookup "c" and the role lookup "r", you do the role lookup first since that is the exclusionary call. So the plain language way to say this is "Select the ids of the users who have the role subscriber, then look up the distinct city names of those users with those ids." – TheGentleman Apr 14 '15 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.