display most commented post in SOME categories

  • stuureenswatnaarhugo

    (@stuureenswatnaarhugo)


    14 years, 7 months ago

    Hi there,

    Ik would like to display the most commented post in say category 1 and 5. Ho do I do that? I already found this code-snippet (which has the option to call it between dates), but don’t know how to make it include (or exclude) categories.

    I hope somebody knows…

    <?php
$result = $wpdb->get_results("SELECT comment_count,ID,post_title, post_date FROM $wpdb->posts WHERE post_date BETWEEN '2008-01-01' AND '2199-12-31' ORDER BY comment_count DESC LIMIT 0 , 10");

foreach ($result as $topten) {
    $postid = $topten->ID;
    $title = $topten->post_title;
    $commentcount = $topten->comment_count;
    if ($commentcount != 0) {
    ?>
         <a href="<?php echo get_permalink($postid); ?>"><?php echo $title ?></a><br />
    <?php }
}
?>

Viewing 1 replies (of 1 total)
  • jceresini

    (@jceresini)

    14 years, 7 months ago

    The following select statement will pull the title and number of comments of each post in a given category. Just replace [cat_id] with the actual category ID.

    select p.post_title,p.comment_count from wp_term_relationships as r left join wp_posts as p on r.object_id=p.id where r.term_taxonomy_id = [cat_id];

    You can use that as a start, but if you want the post with the most comments you can narrow it down with an “order by” and limit statements. For example if you want the top 5 commented posts in a given category you could do:

    select p.post_title,p.comment_count from wp_term_relationships as r left join wp_posts as p on r.object_id=p.id where r.term_taxonomy_id = 6 order by comment_count desc limit 5;

    [signature moderated Please read the Forum Rules]

Viewing 1 replies (of 1 total)
  • The topic ‘display most commented post in SOME categories’ is closed to new replies.