WordPress.org

1. ex272
   Member
   Posted 4 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

2. MichaelH
   Member
   Posted 4 years ago #
   

   Look at using get_the_category().

3. ex272
   Member
   Posted 4 years ago #
   

   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!

4. MichaelH
   Member
   Posted 4 years ago #
   

   Best thing to do is install and activate http://wordpress.org/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;

5. ex272
   Member
   Posted 4 years ago #
   

   Worked like a charm, thanks Michael.

6. EllipsisAeon
   Member
   Posted 3 years ago #
   

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

7. EllipsisAeon
   Member
   Posted 3 years ago #
   

   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;

8. EllipsisAeon
   Member
   Posted 3 years ago #
   

   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.

Topic Closed

This topic has been closed to new replies.