Clever Geek Handbook

MySQL - Skip duplicate WordPress posts

I am writing a script to display the 10 most recent "active" WordPress blog posts (i.e. those with the most recent comments). The problem is that there are a lot of duplicates in the list. I would like to issue duplicates. Is there an easy way to do this by changing the MySQL query (eg IGNORE, WHERE) or some other means? Here's what I have so far:

<?php

function cd_recently_active() {
    global $wpdb, $comments, $comment;
    $number = 10; //how many recently active posts to display? enter here

if ( !$comments = wp_cache_get( 'recent_comments', 'widget' ) ) {
    $comments = $wpdb->get_results("SELECT comment_date, comment_author, comment_author_url, comment_ID, comment_post_ID, comment_content FROM $wpdb->comments WHERE comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT $number");

    wp_cache_add( 'recent_comments', $comments, 'widget' );
}
?>
+1


source to share


2 answers


Look at the DISTINCT parameter for the SELECT statement. Or alternatively the GROUP BY syntax (look at the same link). While they work in different ways, there will be two methods that are most likely to help you get exactly what you want.



+3


source


I thought I figured it out with GROUP BY, but now I'm not sure. Here is the code:

    if ( !$comments = wp_cache_get( 'recent_comments', 'widget' ) ) {
    $comments = $wpdb->get_results("SELECT comment_post_ID, comment_author, comment_date FROM $wpdb->comments WHERE comment_approved = '1' GROUP BY comment_post_ID ORDER BY comment_date_gmt DESC LIMIT $number");
    wp_cache_add( 'recent_comments', $comments, 'widget' );
}

The only change is to add GROUP BY to comment_post_ID (the field I would like to be unique). Unfortunately, this "breaks" the function; it is frozen and not updating.



I also couldn't get DISTINCT to work. One comment I follow to figure this out came from http://www.webmasterworld.com/forum88/9204.htm

Specifically, comment #: 1259236 from ergophobe says, "You left the GROUP BY group. Without that, you will get multiple results for a given topic_id b / c, the row won't be different. In fact, a separate one is not needed, just GROUP BY. "

Still looking ...

0


source






More articles:


All Articles