[Plugin: comments-likes] pulling top 5 likes

Hi maxemil,

thanx for that code. I’ve made a small tweek, so that you will have a number in front of every comment:

function cl_return_top_5()
{
  global $wpdb;
  $counter==0;
  $cl_table_name = $wpdb->prefix . "comments_likes";
  $liketop5 = $wpdb->get_results($wpdb->prepare("SELECT SUM(like_ID),like_comment_ID FROM $cl_table_name GROUP BY like_comment_ID ORDER BY SUM(like_ID) desc LIMIT 5;"));
  foreach ($liketop5 as $acomment)
  {
  	$counter++;
    $id_acomment = $acomment->like_comment_ID;
    $commentauthor = get_comment_author($id_acomment);
    $commentexcerpt = get_comment_excerpt($id_acomment);
    echo '<div class="commentliketop5">';
    echo '<h3>'.$counter.': '.$commentauthor.'</h3>';
    echo '<p><a href="'.get_comment_link($id_acomment).'">'.$commentexcerpt.'</a></p>';
    echo '</div>';
  }
}

Hi Maxemil, Thanks for the code.

I must say I’m completely newbie to mysql queries. Could you please show me how I could limit this query to return ‘likes’ for a specific post, lets say post_ID = ‘1’.

Thanks again

@biralucena
You cant really do that in a sql like this, theres no “page id” in the table.. (these are comment likes, not page/post likes)

Btw: the sql in my first example should be
$liketop5 = $wpdb->get_results($wpdb->prepare("SELECT like_ID, like_comment_ID FROM $cl_table_name GROUP BY like_comment_ID ORDER BY count(like_ID) desc LIMIT 5"));

count, not sum… silly me ??

Hi Maxemil,

Many thanks for your code again

Actually I’ve studied a bit of SQL and I did it!
I’ve noticed also that we should get only the approved comments, in case we need to delete any of them after they’ve received likes.

Here is the code:

function cl_return_top_5($postorpageID)
{
  global $wpdb;
  $cl_table_name = $wpdb->prefix . "comments_likes";
  $wp_comments_table_name = $wpdb->comments;

  $liketop = $wpdb->get_results($wpdb->prepare("SELECT comment_ID
						FROM $wp_comments_table_name
						INNER JOIN $cl_table_name ON comment_ID = like_comment_ID
						WHERE comment_post_ID = '$postorpageID' and comment_approved = '1'
						GROUP BY like_comment_ID
						ORDER BY COUNT( like_ID ) DESC, comment_date DESC
						LIMIT 5")
						);

  foreach ($liketop as $acomment)
  {
    $id_acomment = $acomment->comment_ID;
    $commentauthor = get_comment_author($id_acomment);
    $readmore = '<a href="'.get_comment_link($id_acomment).'">' . __('[Read more]', 'afrec') . '</a>';
    $comment = get_comment_excerpt($id_acomment) . " " .$readmore;
    echo '<p>'.$comment.'</p>';
    echo '<p class="right"><strong>'.$commentauthor.'</strong></p>';

  }
}

//to get the top five comments for the Post or page with ID = 2

<?php cl_return_top_5('2') ?>