0

I have this code which works great.

 <?php 
      $result = $wpdb->get_results("SELECT comment_count, ID, post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 25"); // NUMBER OF POSTS
         foreach ($result as $topten) {
             $postid = $topten->ID;
             $title = $topten->post_title;
             $commentcount = $topten->comment_count;
             if ($commentcount != 0) {
      ?>
        <a href="<?php echo get_permalink($postid); ?>"><span class="tags"> <?php echo $title ?></span></a>
   
   <?php } } ?>

Now it displays (order) the most popular posts by comment count (ALL-TIME).

I know that WordPress understands command 'period':

   period=1hourago

Basically, I'm trying to get the above code working with the period, so that I could define:

24hoursago

or

12hoursago

and so on...

I know I need to somehow integrate this into code:

 'date_query' => [
        [
           
        ]
    ],

But I can't seem to find a way of putting all together.

I saw the idea from: https://www.bitochat.com

Need help.

1 Answer 1

1

So if you want to get the posts that were commented within the last xx hours, e.g. 12 hours, then try one of the following:

Note though, I'm selecting only published posts (post_status = 'publish') and of the post type (post_type = 'post').

And just replace the value of $hours with your preferred number of hours.

  • Using TIMESTAMPDIFF().

    $hours = 12;
    
    $result = $wpdb->get_results( $wpdb->prepare( "
        SELECT p.comment_count, p.ID, p.post_title
            FROM $wpdb->posts p
            INNER JOIN $wpdb->comments c ON c.comment_post_ID = p.ID
        WHERE p.post_type = 'post'
            AND p.post_status = 'publish'
            AND ( TIMESTAMPDIFF( HOUR, c.comment_date, NOW() ) <= %d )
        GROUP BY p.ID
        ORDER BY p.comment_count DESC
        LIMIT 0, 25
    ", $hours ) );
    
  • Or calculate the date and time of xx hours ago and use comment_date >= <date-time xx hours ago>.

    $hours = 12;
    
    $timestamp = current_time( 'timestamp' );
    $date_ago = date( 'Y-m-d H:i:s', $timestamp - $hours * HOUR_IN_SECONDS );
    
    $result = $wpdb->get_results( $wpdb->prepare( "
        SELECT p.comment_count, p.ID, p.post_title
            FROM $wpdb->posts p
            INNER JOIN $wpdb->comments c ON c.comment_post_ID = p.ID
        WHERE p.post_type = 'post'
            AND p.post_status = 'publish'
            AND c.comment_date >= %s
        GROUP BY p.ID
        ORDER BY p.comment_count DESC
        LIMIT 0, 25
    ", $date_ago ) );
    
9
  • hey Sally, thanks for your effort. I have updated my original post so that I could show the code. Could you please take a look at. Sorry I'm completely new, so php it's like creating a new bike for me.
    – robert0
    Commented Mar 21, 2021 at 8:49
  • Try using get_posts() - in your code, change the $query = new WP_Query( to $posts = get_posts( and use foreach ( $posts in place of foreach ( $query->posts. Does it work?
    – Sally CJ
    Commented Mar 21, 2021 at 9:13
  • no :( it displays blank. The interesting part is that when I change 12 hours ago to 80 hours ago, then it displays the same posts as my original code. In other words, all-time posts (which were also made over a month ago) sorted by most comments.
    – robert0
    Commented Mar 21, 2021 at 9:37
  • I have made a few comments just recently, changed to 4hours ago, but it doesn't show those post titles. Blank.
    – robert0
    Commented Mar 21, 2021 at 9:39
  • 1
    Worked perfectly! thanks.
    – robert0
    Commented Mar 21, 2021 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.