Most commented / popular posts and offset

by

I am using this code to show 3 most popular posts based on number of comments :

<?php
            $pop_posts = 3;
                $popularposts = "SELECT $wpdb->posts.ID, $wpdb->posts.post_title,  COUNT($wpdb->comments.comment_post_ID) AS 'stammy' FROM $wpdb->posts, $wpdb->comments WHERE comment_approved = '1' AND $wpdb->posts.ID=$wpdb->comments.comment_post_ID AND post_status="publish" AND comment_status="open" GROUP BY $wpdb->comments.comment_post_ID ORDER BY stammy DESC LIMIT ".$pop_posts;
                $posts = $wpdb->get_results($popularposts);
                if($posts){
                    foreach($posts as $post){
                        $post_title = stripslashes($post->post_title);
                        $guid = get_permalink($post->ID);
                        $thumb = get_post_meta($post->ID,'_thumbnail_id',false);
                        $thumb = wp_get_attachment_image_src($thumb[0], 'popular-posts-image', false);
                        $thumb = $thumb[0]; 
        ?>

                            <?php if ($thumb) { ?>
                                <img src="https://wordpress.stackexchange.com/questions/70027/<?php echo $thumb; ?>" width=80 height=80 />
                            <?php } ?>
                            <h4><a href="<?php echo $guid; ?>" title="<?php echo $post_title; ?>"><?php echo $post_title; ?></a></h4>
                            <div class="clear"></div>

                <?php 
                        }
                }
        ?>

but I want to use this code in two different places, so I want to offset the first 3 posts. Is there any way I can offset the three posts?
thanks

2 Answers
2

Here’s your code wrapped up in a plugin. The important thing is that it prepare()s the input, so the query is safe. You can define 2 arguments: Offset & Limit. Those are basically just the SQL LIMIT.

If you don’t have any use for the plugin, just deactivate it, as it won’t do anything – its output is attached to a filter …

$most_commented = apply_filters( 'wpse70027_display_comments', 0, 3 );
// Now process the result in your template...

… which can simply be put into any template file.

<?php
/**
 * Plugin Name: (#70027) »kaiser« Get Most Commented. 
 * Description: Just place the filter in your themes template file and you're ready to go. 
 */
function wpse70027_get_comments( $offset = 0, $limit = 10 )
{
    global $wpdb;
    static $instance;

    // Validate input
    $offset = absint( $offset );
    $limit  = absint( $limit );
    // Prevent wrong user input
    0 > $offset AND $instance['offset'] = 0;

    // Setup offset/limit as 0/10 on the 1st run
    ! isset( $instance['offset'] ) AND $instance['offset'] = $offset;
    ! isset( $instance['limit'] )  AND $instance['limit']  = $limit;
    // Setup the query string
    ! isset( $instance['query'] )  AND $instance['query']  = 
         "
            SELECT $wpdb->posts.ID, $wpdb->posts.post_title,
                COUNT($wpdb->comments.comment_post_ID) AS cmt
            FROM $wpdb->posts, $wpdb->comments
            WHERE comment_approved  = '1' 
                AND $wpdb->posts.ID = $wpdb->comments.comment_post_ID
                AND post_status="publish" 
                AND comment_status="open" 
            GROUP BY $wpdb->comments.comment_post_ID
            ORDER BY cmt 
            DESC
            LIMIT %d, %d
         ";
    ! isset( $instance['posts'] )  AND $instance['posts']  = array();

    // Three conditions trigger a new query:
    # A) Plugin is running the first time and 'posts' isn't set
    # B) The input offset is below the default offset
    # C) The input limit is above the default limit
    if (
        ! isset( $instance['posts'] )
        OR ! in_array( $offset, range( $instance['offset'], $instance['limit'] ) )
        OR ! in_array( $limit, range( $instance['offset'], $instance['limit'] ) )
        )
    {
        // Adjust the range
        $instance['offset'] = $instance['offset'] > $offset ? $offset : $instance['offset'];
        $instance['limit']  = $instance['limit']  < $limit  ? $limit  : $instance['limit'];

        $instance['posts'] = $wpdb->get_results( $wpdb->prepare(
             $instance['query']
            ,$instance['offset']
            ,$instance['limit']
        ) );
    }

    // Only return what was requested
    return array_intersect_key(
         $instance['posts']
        ,range( $offset, $limit )
    );
}
add_filter( 'wpse70027_display_comments', 'wpse70027_get_comments', 10, 2 );

And no, there’s no difference in performance when you put it into your functions.php file. The only thing is that this code will be gone on theme update or change, so better leave it inside a plugin.

Leave a Comment