How to get a meta values from all posts in a special category

  • Resolved jgme

    (@jgme)


    11 years, 7 months ago

    Hi, I want to display all values of a meta key (custom field).
    With this function I can display all the values, but I can’t specify a category:

    function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
    global $wpdb;
    if( empty( $key ) )
        return;
    $r = $wpdb->get_results( $wpdb->prepare( "
        SELECT p.ID, 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 ));

    foreach ( $r as $my_r )
        $metas[$my_r->ID] = $my_r->meta_value;

    return $metas;
}

    I found this Code-Snippet for making a query for a special category:

    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->terms.name = 'mycategory_slug'
AND $wpdb->term_taxonomy.taxonomy = 'category'

    Anyone an Idea how to put these two function together? I want to put in the cat_ID instead of the slug. Thanks in advance!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator keesiemeijer

    (@keesiemeijer)

    11 years, 7 months ago

    Try it with this. Change $catid to the correct category ID:

    function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {

    $tax = 'category';
    $catid = 1;

    global $wpdb;
    if( empty( $key ) )
        return;
    $r = $wpdb->get_results( $wpdb->prepare( "
        SELECT p.ID, pm.meta_value FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
        LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
        WHERE pm.meta_key = '%s'
        AND p.post_status = '%s'
        AND p.post_type = '%s'
        AND tt.taxonomy = '%s'
        AND tt.term_id = '%d'
    ", $key, $status, $type,$tax,$catid ));

    foreach ( $r as $my_r )
        $metas[$my_r->ID] = $my_r->meta_value;

    return $metas;
}

    Thread Starter jgme

    (@jgme)

    11 years, 7 months ago

    You are my hero. Thanks for the help, it works!
    I have changed the code on line 14 from AND tt.term_id = ‘%d’ to AND tt.term_id IN($catid) because with the other one I can only get one $catid now I can put in more more.

    function get_meta_values( $key = '', $catid = '' , $type = 'post', $status = 'publish', $tax = 'category') {
    global $wpdb;
if( empty( $catid ) ) $catid = '1';
    if( empty( $key ) )
        return;
    $r = $wpdb->get_results( $wpdb->prepare( "
        SELECT p.ID, pm.meta_value FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
        LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
        WHERE pm.meta_key = '%s'
        AND p.post_status = '%s'
        AND p.post_type = '%s'
        AND tt.taxonomy = '%s'
        AND tt.term_id IN($catid)
    ", $key, $status, $type, $tax, $catid ));

    foreach ( $r as $my_r )
        $metas[$my_r->ID] = $my_r->meta_value;

    return $metas;
}

    Get Values:
    $myarray = get_meta_values( 'my_meta_key', '1,2' );

    Moderator keesiemeijer

    (@keesiemeijer)

    11 years, 7 months ago

    You’re welcome. I’m glad you got it resolved 🙂

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘How to get a meta values from all posts in a special category’ is closed to new replies.