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 a wordpress plugin called "kf most read" which stores a count of how many times a post was read, and lets you output a list of most read posts.

This works well. The issue is, I am trying to pull the most read posts, but only the most read posts within the current category you are viewing.

I am close to clueless when it comes to sql.

Here us what the plugin is currently using to pull the most read posts:

$sql = "SELECT count(mr.post_ID) AS totHits, p.ID, p.post_title FROM $wpdb->posts p 
JOIN {$wpdb->prefix}kf_most_read mr ON mr.post_ID = p.ID WHERE mr.hit_ts >= '".(time() - ( 86400 * $period))."'
GROUP BY mr.post_ID ORDER BY totHits DESC, ID ASC LIMIT $limit";

How could I incorporate the below query which pulls from a specific category into the above?

$sql .= "LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)";
$sql .= "WHERE $wpdb->term_taxonomy.term_id IN ($currentcat)";
$sql .= "AND $wpdb->term_taxonomy.taxonomy = 'category'" ;

Any Help on this would be much appreciated.

share|improve this question

1 Answer 1

You need to make sure that the $currentcat variable is set before this query runs. This should be the id of the category you want to pull top posts from.

$sql = "SELECT count(mr.post_ID) AS totHits, p.ID, p.post_title FROM $wpdb->posts p 
JOIN {$wpdb->prefix}kf_most_read mr ON mr.post_ID = p.ID 
JOIN {$wpdb->prefix}term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE mr.hit_ts >= '".(time() - ( 86400 * $period))."' 
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN ($currentcat)
GROUP BY mr.post_ID ORDER BY totHits DESC, ID ASC LIMIT $limit";
share|improve this answer
    
As much as I hate the idea of including direct SQL queries in a plug-in (use the WP_Query object!!!), this is the right answer. –  EAMann Feb 18 '11 at 17:42
    
@EAMann I agree, try to use the query_vars object first. If that doesn't give you all you need, inject your custom SQL through filters. –  wyrfel Feb 19 '11 at 8:25

Your Answer

 
discard

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