WordPress.org

Ready to get started?Download WordPress

Forums

comments-likes
pulling top 5 likes (5 posts)

  1. maxemil
    Member
    Posted 3 years ago #

    Just a small function you can add to pull the 5 most liked comments

    function cl_return_top_5()
    {
      global $wpdb;
      $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)
      {
        $id_acomment = $acomment->like_comment_ID;
        $commentauthor = get_comment_author($id_acomment);
        $commentexcerpt = get_comment_excerpt($id_acomment);
        echo '<div class="commentliketop5">';
        echo '<h3>'.$commentauthor.'</h3>';
        echo '<p><a href="'.get_comment_link($id_acomment).'">'.$commentexcerpt.'</a></p>';
        echo '</div>';
      }
    }

    http://wordpress.org/extend/plugins/comments-likes/

  2. Achim Meurer
    Member
    Posted 3 years ago #

    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>';
      }
    }
  3. biralucena
    Member
    Posted 3 years ago #

    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

  4. maxemil
    Member
    Posted 3 years ago #

    @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 ;)

  5. biralucena
    Member
    Posted 3 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic