show number of open comments on custom dashboard

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:

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.";