Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question

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.

share|improve this answer
1  
You could just fetch all posts at once and store posts 4—6 for the second call. Saves one query. –  toscho Oct 22 '12 at 1:50
    
@toscho Right, but not exactly. It should check if the offset/limit increased/decreased. Then if should modify the LIMIT dynamically and only query those results that have been called in the last run. This way it could be used for dozens of calls without a real performance decrease. –  kaiser Oct 22 '12 at 2:05
    
Pass the total number of needed posts in the first call and the amount of returned posts in each call. Still just one query and flexible enough. –  toscho Oct 22 '12 at 2:07
    
thanks for your help, I think this code does not show a thumbnail of the posts, the code I have shows thumbnail as well, is it possible to modify the above code? –  Andy M Oct 22 '12 at 2:58
    
also the code gives fatal error when activating it as a plugin –  Andy M Oct 22 '12 at 3:02

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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