June 17, 2012

Query WPMU Comment Count For A List Of Blogs

Recently created a new blog list function for PrimaryBlogger to allow users to see their current blogs in a lightbox iFrame, I thought it would also be nice to have a pending comment count on the page. get_blogs_of_user does not support comment count or much else apart from giving you basic information about the users accessible blogs. I had to get creative due to us being on a Multi-DB install and $wpdb would not use anything from the foreach loop created for use with get_blogs_of_user.

Here’s how I accomplished it:

/* DB init */
global $wpdb;
/* Get current user details and declare variables to pull blog info */
$current_user = wp_get_current_user();
$user_id = $current_user->id;
$user_blogs = get_blogs_of_user($user_id);
/* Go through the list of blogs and create a list element for each */
foreach ($user_blogs AS $user_blog) {
/* Get the blog id for the current loop point */
$blog_comment_id = $user_blog->userblog_id;
/* MD5 hash the blog_id for use with Multi-DB, remove the last 30 characters - we only need the first 2! */
$db_id = substr(md5($blog_comment_id), 0, -30);
/* Create the SQL statement to pull the current loop point's blog pending comments and store it as $request */
$sql = "SELECT COUNT(*) FROM db-prefix_{$db_id}.wp-prefix_{$blog_comment_id}_comments WHERE comment_approved = '0'";
$request = $wpdb->get_var( $wpdb->prepare( $sql ) );

Then we just need to put it on the page…

<a href="siteurl; ?>/wp-admin/edit-comments.php?comment_status=moderated" title=" Comments awaiting moderation" target="_blank"></a>

Leave a Reply

Your email address will not be published. Required fields are marked *