WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Calculate number of posts with a specific custom field and a specific category (10 posts)

  1. csseur3
    Member
    Posted 5 years ago #

    Hello,

    i have many posts with a custom field called "test", i want to calculate the total number of this posts and show it on my pages, how to do that?

    bye

  2. MichaelH
    Member
    Posted 5 years ago #

    Could use the wpdb class for that:

    <?php
    $test_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_key = 'test';");
    echo '<p>test count is ' . $test_count . '</p>';
    ?>
  3. csseur3
    Member
    Posted 5 years ago #

    Thanks!

    i try to limit this at a category but nothing is show:

    $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE term_ID='3' AND meta_key = 'actu';");

    what is wrong?

    bye

  4. csseur3
    Member
    Posted 5 years ago #

    oups, i think have missed one thing, but when i try the new query

    $test_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_key = 'actu' AND FROM $wpdb->terms WHERE term_ID=133");

    nothing again :(

  5. MichaelH
    Member
    Posted 5 years ago #

    You will want to consider the information described here:
    http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query#Query_based_on_Custom_Field_and_Category

    Note: also changed title to reflect you want custom field and category test

  6. csseur3
    Member
    Posted 5 years ago #

    i try

    `<?php
    $test_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    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)
    WHERE $wpdb->term_taxonomy.term_id = 133
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'actu'
    ORDER BY $wpdb->postmeta.meta_value ASC");
    echo '' . $test_count . '';
    ?>`

    but the categori with ID 133 have many sub-categories and the count works only on the sub-category..; this is normal? i have many subcategories and i search a more friendly method to do that please.

    bye

  7. MichaelH
    Member
    Posted 5 years ago #

    Use get_categories to get an array of child categories and then include those categories in your query.

  8. csseur3
    Member
    Posted 5 years ago #

    i have this code:

    <?php
          $cat = get_query_var('cat');
          $categories=get_categories('orderby=name&exclude=9,133,136,97,105,106,109,110,112,113,98,116,117,118,104,103,131,119,120,101,122,123,125,96,95,126,127,128,134,135,132&child_of='.$cat);
          if ($categories) {
            foreach($categories as $term) {
              echo '' . $term->cat_ID .' ';
            }
          }
          ?>

    how to include that in the sql query?

    bye

  9. Austin Matzko
    Member
    Posted 5 years ago #

    If you want to know the number of posts in categories 1,2, and 3 that have the meta_key of "actu", then you can do the following:

    $posts_with_actu = get_posts(array(
            'category__in' => array(1,2,3),
            'meta_key' => 'actu',
            'showposts' => -1,
    ));
    
    printf('I have %d posts with "actu"!', count($posts_with_actu));
  10. csseur3
    Member
    Posted 5 years ago #

    Thanks filosofo!

    But with "SELECT SUM" operation, use "WHERE $wpdb->term_taxonomy.term_id" with multiple categories is always impossible.

    Thanks for solution for "SELECT COUNT" operation, i read the forum for a solution for "SELECT SUM" :)

    ++

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.