Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am trying to get popular posts using the coment count. I also want to exclude some categories from the query. Any idea how this can be achieved.

What would be query to exclude particular categories? for example i want the query should not include category names health and auto

SELECT ID, post_title, COUNT($wpdb->comments.comment_post_ID) AS 'stammy' FROM $wpdb->posts, $wpdb->comments WHERE comment_approved = '1' AND $wpdb->posts.ID=$wpdb->comments.comment_post_ID AND post_status = 'publish' AND post_date < '$now' AND post_date

share|improve this question

2 Answers 2

There are 3 available functions in WordPress you can use to do this.. query_posts, get_posts or WP_Query to return a selection of posts ordered by the comment count, no need for the SQL query..

<?php
$my_query = new WP_Query;
$my_query->query( array( 
    'cat' => '1,2,3,-4,-5,-6',
    'orderby' => 'comment_count',
    'order' => 'desc'
) );
if( $my_query->have_posts() ) :
    while( $my_query->have_posts() ) : $my_query->the_post();

        ?>
        <div id="post-<?php the_ID(); ?>" <?php post_class(); ?>>
            <?php the_title(); ?><br />
            <?php the_content(); ?>
        </div>
        <?php

    endwhile;
endif;
wp_reset_query();
?>

1, 2 and 3 are categories to include, 4, 5 and 6 are exclusions, the negative value indicates an exclusion, normal non-negatives are inclusions.

See here for other possible parameters for the query.
http://codex.wordpress.org/Function_Reference/query_posts

Also here for information on tags used inside the post loop(the_title, the_content, etc). http://codex.wordpress.org/Template_Tags#Post_tags

Hope that helps... :)

share|improve this answer

I done this while time ago. On my blog You have solution, I know its in polish but code is code :) http://blog.grabek-adam.pl/2010/06/wordpress-popularne-posty-plugin-do-obrazkw/

Here You have just query:

$posty = $wpdb->get_results("SELECT SQL_CALC_FOUND_ROWS wp_posts.id, wp_posts.post_title, wp_posts.comment_count, wp_posts.post_date
                        FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
                        INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
                        WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'
                        AND wp_term_taxonomy.term_id IN ('cat_id1,cat_id2,cat_id5')
                        AND wp_posts.post_type = 'post'
                        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
                        AND wp_posts.comment_count > 0
                        GROUP BY wp_posts.ID ORDER BY wp_posts.comment_count DESC
                        LIMIT 5
                        ");

This code will only include categories that You specify in here AND wp_term_taxonomy.term_id IN ("cat_id1,cat_id2,cat_id5") but i think it will be easy to change for Your requirements

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.11.24.2041\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["ER BY wp_posts.comment_count DESC\n                        LIMIT 5\n                        \");\n\nThis code will only include categories that You specify in here AND wp_term_taxonomy.term_id IN (\"cat_id1,cat_id2,cat_id5\") but i think it will be easy to change for Your requirements\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}