Display amount of pending posts in back/front end

Question

I want to display the total amount of pending posts that my site has. My site is a single site, not a network. I am looking to echo that count in the back-end (a custom plugin page) and in the front-end.

How can I do this?

Answer 1

Easy native SQL query:

global $wpdb;

$sql = <<<SQL
SELECT post_status, COUNT( * ) AS count
    FROM {$wpdb->posts}
WHERE post_status = 'pending'
SQL;

$result = $wpdb->get_results( $sql );

The result will be something like

+ ----------- + ----- +
| post_status | count |
+ ----------- + ----- +
|   pending   |  158  |
+ ----------- + ----- +

So you'll be able to show the number of pending posts with $result->count.

is_user_logged_in() checks if a user is logged in.

And with edit_post_link() you'll be able to link to those posts directly (after you queried for them separately). The following will link to the posts admin table filtered by post status pending:

printf( '<a href="%s">Pending Posts</a>', admin_url( 'edit.php?post_status=pending' ) );

Plugin

As a plugin:

<?php
/**
 * Plugin Name: (#115946) Post Count by status
 * Plugin URI:  http://wordpress.stackexchange.com/questions/115946
 * Author:      Franz Josef Kaiser <[email protected]>
 * Author URI:  http://unserkaiser.com
 */

function wpse115946PostCountByStatus( $status )
{
    global $wpdb;

$sql = <<<SQL
SELECT post_status, COUNT( * ) AS count
    FROM {$wpdb->posts}
WHERE post_status = %s
SQL;

    $result = array_shift( $wpdb->get_results( $wpdb->prepare( $sql, $status ) ) );

    $title = sprintf( _e( '%s %s posts', ucwords( $status ), $result->count ) );
    return printf(
        '<a href="%1$s" title="%2$s">%2$s</a>',
        admin_url( 'edit.php?post_status=pending' ),
        $title
    );
}