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>