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
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.
