2

Been looking for a clean and simple way to produce comments from across a network using the $wpdb database class.

I did find this answer, which has been protected, and is four years old.

I wrapped it in a function and attempted to call it on the front page of my network, but it didn't work. I also played with the mysql statement, but could not get it to produce any output.

Here's what I have:

function display_sitewide_comments() {

global $wpdb;
$number = 20; // maximum number of comments to display
$selects = array();
$args = array();
foreach (wp_get_sites( $args ) as $blog)
   // select only the fields you need here!
   $selects[] = "(SELECT comment_post_ID, comment_author, comment_author_email, comment_date_gmt, comment_content, post_title, {$blog['blog_id']} as blog_id FROM {$wpdb->base_prefix}{$blog['blog_id']}_comments
      LEFT JOIN {$wpdb->base_prefix}{$blog['blog_id']}_posts
      ON comment_post_id = id
      WHERE post_status = 'publish'
        AND post_password = ''
        AND comment_approved = '1'
        AND comment_type = ''
       ORDER BY comment_date_gmt DESC LIMIT {$number})"; // real number is (number * # of blogs)

  $comments = $wpdb->get_results(implode(" UNION ALL ", $selects)." ORDER BY comment_date_gmt DESC", OBJECT);

$count = 0;
foreach((array)$comments as $comment):
  $count++;
  if($count == $number+1) break; 
  ?>
  <li>
   <?php echo get_avatar($comment->comment_author_email, 32); ?>
   <a href="<?php echo get_blog_permalink($comment->blog_id, $comment->comment_post_ID); ?>" title="commented on <?php echo strip_tags($comment->post_title); ?>">
   <?php echo $comment->comment_author; ?> wrote: 
   <?php echo convert_smilies(wp_trim_excerpt($comment->comment_content)); ?>
   (<?php echo human_time_diff(strtotime("{$comment->comment_date_gmt}")); ?>)  
   </a>
  </li>
<?php
endforeach;
}

I'm wondering, since the post is 4 years old, if something has changed in core, or the database structure to make the query obsolete? Or possibly there is an error that I cannot see. In debug mode it does not throw any errors. I also declared and used an empty $args array as was suggested in the original post.

Thought this was a very useful function and that more people than me were having trouble with it.

Thanks.

  • Do you really have a closing ?> before the closing } (second last and last line)? – tfrommen Apr 17 '14 at 20:46
  • No I do not, thank you for catching that. I've made the correction. – jw60660 Apr 17 '14 at 21:01
2

Why not use get_comments(), instead of trying to roll your own with $wpdb?

function display_sitewide_comments() {
    $sites = wp_get_sites();
    $network_comments = array();
    $max = 20;
    foreach( $sites as $site ) {
        switch_to_blog( $site->blog_id );    
        $args = array(
        'number' => $max,
            'status' => 'approved',
    );
        $network_comments[$site->blog_id] = get_comments( $args );
        restore_current_blog();
    }
    // inspect the comments
    var_dump( $network_comments );
}

Edited to add:

If you just want the 20 most recent comments, you could change $network_comments[$site->blog_id] = get_comments( $args ); to something like

$comments = get_comments( $args );
foreach( $comments as $comments ) {
    $network_comments[] = $comment;
}

Then, at the end of the loop, use usort() to sort the comments based on $comment->comment_date_gmt and then truncate it to $max values.

References

  • What about speed of this code? Isn't it very slow? AFAIR switch_to_blog is rather time-consuming... – Krzysiek Dróżdż Apr 18 '14 at 10:07
  • I was going to ask the same thing. But what if I use the Transients API to cache it? And, if my network runs in the low hundreds, is it still an issue? – jw60660 Apr 18 '14 at 12:25
  • Fair enough, switch_to_blog() might slow things down. Using the Transients API to cache would definitely speed things up. OTOH, you could use transients or the Options API when the comment is approved -- hook to comment_unapproved_to_approved that does an update_site_option(), for instance. – Pat J Apr 18 '14 at 14:54
  • It's a step in the right direction. Would be nice if there was a function for this in core. Much like wp_get_sites(). – jw60660 Apr 22 '14 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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