0

I want to retrieve meta_values applied to posts.

Currently I execute the following query

global $wpdb;
$table = $wpdb->prefix.'postmeta';
$values = $wpdb->get_results(
     "SELECT meta_value FROM $table WHERE meta_key = '$metakey' GROUP BY meta_value",
      ARRAY_A);

This returns all possible meta_values. Is there any possibility to retrieve only the meta_values of posts with post_status published (and not private).

1 Answer 1

4

Yes, do an INNER JOIN, something like this should work:

$values = $wpdb->get_col($wpdb->prepare("
    SELECT meta_value FROM $wpdb->postmeta
    INNER JOIN $wpdb->posts
      {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id
    WHERE meta_key= %s 
    AND {$wpdb->posts}.post_status='publish'"
,$metakey
));

I changed $wpdb->get_results() to $wpdb->get_col() since it looks like you are just selecting a single column of data as an array. I also added $wpdb->prepare() since that is generally best practice.

1
  • Thanks, got it working for me now. My final SELECT-statement looked like SELECT DISTINCT meta_value FROM $wpdb->postmeta INNER JOIN $wpdb->posts ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id WHERE meta_key = %s AND {$wpdb->posts}.post_status = 'publish' AND {$wpdb->posts}.post_type = 'event' ORDER BY meta_value"
    – pbaldauf
    Apr 21, 2015 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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