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') ?>
