show number of open comments on custom dashboard

by

On my custom dashboard, I want to show the number of total new comments (somehow like “right now”). Just like “You have … open comments to take care of”. So all I want is the total number not a list for each post or so.

But I can’t get this right. Any help out there?

In the meantime I got some info (from wpmudev) on this, telling me that this is heavy stuff and not a very basic question:

This is less basic than you might think.

You need to:

  • Register a new meta box to the WP admin
  • Query the database for the total new comments since the currently logged in user’s last visit
  • Set the visit timeout (so changing page doesn’t just set them to 0 right away, that would be frustrating)
  • Write the CSS to make the display pretty
  • You’ll find hints as to what to do if you examine the WordPress core in the “Right Now” widget but that’s probably going to be over your head.

At that point, you may as well publish it as a plugin. It’s possible and as far as plugins go, it’s barely intermediate, but it’s still very advanced for this forum.*

UPDATE

This is what finally made me happy, thanks again to @toscho. It is showing the number of comments, that are waiting for moderation. It can be placed everywhere you want it to show up.

<?php
function t5_count_new_comments()
{
global $wpdb;

// last user access
$last_access   = get_user_meta( get_current_user_id(), 'last_access', TRUE );

// comment query
$where = $wpdb->prepare( "WHERE comment_date > %s AND comment_approved='0'",   $last_access );
$comment_query = $wpdb->get_results(
    "SELECT comment_ID,
    COUNT( comment_ID ) AS new_comments
    FROM {$wpdb->comments} $where",
    OBJECT
    );

if ( ! isset ( $comment_query[0]->new_comments ) )
    return 0;

return $comment_query[0]->new_comments;

}

$new_comments = t5_count_new_comments();
echo "There are $new_comments new comments.";
?>

1 Answer
1

Actually, it is not that hard.

  • The last access time for a user is in get_user_meta( get_current_user_id(), 'last_access', TRUE ).
  • The date of each comment is in the column comment_date.
  • Both share the same format, so we can compare them in SQL with a simple >.
  • There is an action in the Right Now dashboard widget to show additional rows: right_now_discussion_table_end. See the file wp-admin/includes/dashboard.php.

Now let’s stick it together:

<?php  # -*- coding: utf-8 -*-
/**
 * Plugin Name: T5 New Comments In Right Now Dashboard
 * Description: Show the number of new comments on the Right Now dashboard
 * Plugin URI:
 * Version:     2013.03.16
 * Author:      Thomas Scholz
 * Author URI:  http://toscho.de
 * Licence:     MIT
 * License URI: http://opensource.org/licenses/MIT
 */

add_action( 'right_now_discussion_table_end', 't5_new_comments_right_now' );

function t5_new_comments_right_now()
{
    global $wpdb;

    // last user access
    $last_access   = get_user_meta( get_current_user_id(), 'last_access', TRUE );

    // comment query
    $where         = $wpdb->prepare( "WHERE comment_date > %s", $last_access );
    $comment_query = $wpdb->get_results(
        "SELECT comment_ID,
            COUNT( comment_ID ) AS new_comments
            FROM {$wpdb->comments} $where",
            OBJECT
    );

    // default values
    $num  = 0;
    $text = _x(
        'New comments',
        'no new comments on dashboard right now',
        'plugin_t5_new_comments'
        );

    // overwrite default values
    if ( isset ( $comment_query[0]->new_comments ) ) {
        $num = $comment_query[0]->new_comments;
        $text = _n( 'New comment', 'New comments', $num, 'plugin_t5_new_comments' );
    }

    // prepare for display
    $num  = number_format_i18n( $num );
    $num  = "<a href="https://wordpress.stackexchange.com/questions/91148/edit-comments.php"><span class="total-count">$num</span></a>";
    $text = "<a href="https://wordpress.stackexchange.com/questions/91148/edit-comments.php">$text</a>";

    // display extra column
    printf(
        '<tr>
            <td class="b b-comments">%1$s</td>
            <td class="last t comments">%2$s</td>
        </tr>',
        $num,
        $text
    );
}

Result:

screenshot

Download from GitHub

In response to your comments: To get just the number of new comments a an integer, use something like this:

function t5_count_new_comments()
{
    global $wpdb;

    // last user access
    $last_access   = get_user_meta( get_current_user_id(), 'last_access', TRUE );

    // comment query
    $where         = $wpdb->prepare( "WHERE comment_date > %s", $last_access );
    // to get unapproved comments only use this instead:
    // $where         = $wpdb->prepare( "WHERE comment_date > %s AND comment_approved='0'", $last_access );
    $comment_query = $wpdb->get_results(
        "SELECT comment_ID,
        COUNT( comment_ID ) AS new_comments
        FROM {$wpdb->comments} $where",
        OBJECT
        );

    if ( ! isset ( $comment_query[0]->new_comments ) )
        return 0;

    return $comment_query[0]->new_comments;
}

Now you can use that function in your custom code:

$new_comments = t5_count_new_comments();
echo "There are $new_comments new comments.";

Leave a Comment