PHP / mySQL – Query to Retrieve Only Unique Meta Values

1 Views

I need help in rewriting the query below to accept a second and a second + third qualification.

Example meta keys and values:

Read More
Trees
meta_key = trees
meta_value = pine, oak, sequoia

States
meta_key = states
meta_value = california, washington, florida

Countries
meta_key = countries
meta_value = usa, uk, ireland

I attempted to add these AND statements but I was not able to cause it to work properly.

Second qualification: 

AND (pm.meta_key = 'states' AND pm.meta_value = 'california')

Second and third qualification: 

AND (pm.meta_key = 'states' AND pm.meta_value = 'california')
AND (pm.meta_key = 'countires' AND pm.meta_value = 'usa')

Function with query I need help in rewritting: 

function get_unique_post_meta_values( $key = 'trees', $type = 'post', $status = 'publish' ) {
    global $wpdb;
    if( empty( $key ) )
        return;
    $res = $wpdb->get_col( $wpdb->prepare( "
SELECT DISTINCT pm.meta_value FROM {$wpdb->postmeta} pm
LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
WHERE pm.meta_key = '%s'
AND p.post_status = '%s'
AND p.post_type = '%s'
", $key, $status, $type ) );
    return $res;
}

Thanks in advance for your help! Please let me know if further clarification is required.

Post Views: 1

Related posts

1 comment

  1. If the function sets $key=trees, what is the point of an empty check? Additionally, I think you are missing the curly brackets. You are selecting ‘pm.meta_value’, which is why are you doing a LEFT JOIN, I think? Maybe seeing your DB structure would help me understand that, but an INNER JOIN is what you want.

    if( empty( $key ) ){
 return;
} else {
    $res = $wpdb->get_col( $wpdb->prepare( "
SELECT DISTINCT pm.meta_value FROM {$wpdb->postmeta} pm
INNER JOIN {$wpdb->posts} as p ON p.ID = pm.post_id
WHERE pm.meta_key = '%s'
AND p.post_status = '%s'
AND p.post_type = '%s'
    ", $key, $status, $type ) );
    return $res;
}

    Try this. If it doesn’t work then update this question with more details and I will help guide you further.

Comments are closed.