how to get popular posts fro selected categories i

Question

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

Answer 1

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

Answer 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... :)