Popular Posts by Comments with Categories

  • Resolved ex272

    (@ex272)


    16 years ago

    Hi,

    I am trying to place on my sidebar an area listing the most commented, i.e. popular, posts, which I was able to do with a basic wpdb query. But I am having the problem of getting the category names for those posts, arising from the obsoleteness of “post_category.”

    Here is the code I currently have:

    global $wpdb ;
$result = $wpdb->get_results ( "SELECT comment_count, ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' ORDER BY comment_count DESC LIMIT 0 , 5" ) ;
foreach ( $result as $post )
{
	setup_postdata ( $post ) ;
	$postid = $post->ID ;
	$title = $post->post_title ;
	$category = $post->post_category ;
	$commentcount = $post->comment_count ;
	echo '<li><a href="' . get_permalink($postid) . '">in ' . $category . ': ' . $title . ' (' . $commentcount . ')</a></li>' ;
}

    I know category information is now stored in separate tables. How can I retrieve that information in the same query?

    Thanks

Viewing 7 replies - 1 through 7 (of 7 total)
  • MichaelH

    (@michaelh)

    Look at using get_the_category().

    Thread Starter ex272

    (@ex272)

    Hi Michael,

    So since this is a sidebar function, it seems that rather than using a wpdb query, I’d have to execute The Loop in the sidebar as well as the content area, in order to call get_the_category() from the sidebar, correct? Then how would I grab the top 5 posts ordered by comment count within this sidebar Loop? I looked into query_posts(), but did not see comment_count as an option for its orderby parameter.

    Sorry, this is only my second day at this.

    Thanks again!

    MichaelH

    (@michaelh)

    Best thing to do is install and activate https://www.ads-software.com/extend/plugins/most-commented then just put this in your sidebar:

    <?php mdv_most_commented(); ?>

    If you insist on the category displaying then in most-commented.php change

    $permalink = get_permalink($post->ID);
$mdv_most_commented .= $before . '<a href="' . $permalink . '" title="' . $post_title.'">' . $post_title . '</a> (' . $comment_count.')' . $after;

    to

    $permalink = get_permalink($post->ID);
$category = get_the_category($post->ID);
$first_category = $category[0]->cat_name;
$mdv_most_commented .= $before . '<a href="' . $permalink . '" title="' . $post_title.'">' . $post_title . '</a> (' . $comment_count . ' in ' . $first_category . ')' . $after;

    Thread Starter ex272

    (@ex272)

    Worked like a charm, thanks Michael.

    Another thanks to Michael for the code, very helpful in getting one step closer to what I’m trying to do ??

    Back with an update on this. If you would like to turn that category name into an actual link to the category from the comment, here’s the code:

    $permalink = get_permalink($post->ID);
$category = get_the_category($post->ID);
$first_category = $category[0]->cat_name;

$category_id = get_cat_ID($first_category);
$category_link = get_category_link($category_id);

$mdv_most_commented .= $before . '<a href="' . $category_link . '" title="' . $first_category .'">' . $first_category . '</a>:&nbsp;<a href="' . $permalink . '" title="' . $post_title.'">' . $post_title . '</a> (' . $comment_count . ')' . $after;

    ok, stuck again. I basically want to sort the categories by name then group the posts below the category name/link in a semi-forum layout fashion:

      Category 1:

    • posts (#of comments)
    • posts (#of comments)
    • posts (#of comments)
      Category 2:

    • posts (#of comments)
    • posts (#of comments)

    I’m thinking this would be done with something like “foreach $category_id” but am stuck on the implementation; I think my caffiene levels are low.

    Any tips/links/help is greatly appreciated.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Popular Posts by Comments with Categories’ is closed to new replies.