Most commented / popular posts and offset

Question

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="<?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

Answer 1

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.

Answer 2

You could use wordpress query to have multiple loops in one page and offset the count in the second loop.

<?php 
                     $args = array(
                        'posts_per_page' => 3,
                        'orderby' => 'comment_count'
                        );

                        $the_query = new WP_Query( $args );

?>

    <?php if ( $the_query->have_posts() ) : while ( $the_query->have_posts() ) : $the_query->the_post(); ?>

     //Put what you want to output here

    <?php 

        endwhile; 

        endif; ?>

<?php wp_reset_postdata(); ?>

Then you just offset it in the second query.

<?php 

                   $args = array(
                        'posts_per_page' => 3,
                        'orderby' => 'comment_count',
                        'offset'  => 3
                        );

                        $the_query = new WP_Query( $args );



  ?>
    <?php if ( $the_query->have_posts() ) : while ( $the_query->have_posts() ) : $the_query->the_post(); ?>

     //Put what you want to output here

    <?php 

        endwhile; 

        endif; ?>

http://codex.wordpress.org/Class_Reference/WP_Query#Post_.26_Page_Parameters