Grabbing how Many Posts by Month for a Dashboard Widget

Question

I am trying to develop a dashboard widget that counts posts or post types by month. I am really not sure how to go about doing this.

I know how to create a dashboard widget and all that with post counts, post type counts.

I even have a custom one that lists the most recent post types i have. But I have yet to create one with just counting how many posts or post types there are in a month and I am just not sure how to do it.

Any ideas and help on this would be awesome. Thank you. :)

I would like it to be similar to something like this:

This is an update with the script below. However I cannot get it to work.

add_action('wp_dashboard_setup', 'dashboard_test');

function dashboard_test() {
global $wp_meta_boxes;

wp_add_dashboard_widget('month_dashboard', 'Reports Submitted for the Year',   'custom_test');
}

function custom_test() {
global $wpdb;
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE   post_status = 'publish' AND post_type = 'report' ORDER BY post_date DESC");

foreach($years as $year) 

    $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'report' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
    
    foreach($months as $month)  

            if ($month < 10)
            {
                $month = '0' . $month; 
            } 
            
            $theids = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'report' AND MONTH(post_date)= '".$month."' AND YEAR(post_date) = '".$year."' ORDER BY post_date ASC");
            
            echo '<h4>';
                    
                        echo '<li><a href="/report/'.$year.'/'.$month.'">' . date( 'F', mktime(0, 0, 0, $month) ). ' (' . count($theids) . ')</a></li>' ;
                   
                echo '</h4>';
                
}

I get this error: Call to a member function get_col() on a non-object in /var/www/vhosts/hibsterlite.com/httpdocs/wp-content/themes/hibsterlite/functions‌​.php on line 209

Ok I no longer get the error above however I only get one month listed atm. Any ideas guys? :)

Answer 1

Just Went over your code and it looks fine but you are missing the code blocks for your foreach loops so:

add_action('wp_dashboard_setup', 'dashboard_test');
function dashboard_test() {
    global $wp_meta_boxes;
    wp_add_dashboard_widget('month_dashboard', 'Reports Submitted for the Year',   'custom_test');
}

function custom_test(){
    global $wpdb;
    $years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE   post_status = 'publish' AND post_type = 'report' ORDER BY post_date DESC");
    foreach($years as $year){
        $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'report' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
        foreach($months as $month) {
            $month = ($month < 10)? '0' . $month : $month; 
            $theids = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'report' AND MONTH(post_date)= '".$month."' AND YEAR(post_date) = '".$year."' ORDER BY post_date ASC");
            echo '<h4>';
            echo '<li><a href="/report/'.$year.'/'.$month.'">' . date( 'F', mktime(0, 0, 0, $month) ). ' (' . count($theids) . ')</a></li>' ;
            echo '</h4>';
        }
    }
}

and it works just fine:

Answer 2

I implemented something similar for a custom post type archive, because I didn't like the handling of wp_get_archives. Basically I created a custom sql query for all posts in a year, then broke that down by month, then performed a count of the posts each month.

<ul>

    <?php

    $years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'mediaposts' ORDER BY post_date DESC");

    foreach($years as $year) :

        $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'mediaposts' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");

            foreach($months as $month) : 

                if ($month < 10)
                {
                    $month = '0' . $month; 
                } 

                $theids = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'mediaposts' AND MONTH(post_date)= '".$month."' AND YEAR(post_date) = '".$year."' ORDER BY post_date ASC");

                    ?>

                    <h4>
                        <?php 
                            echo '<li><a href="/mediaposts/'.$year.'/'.$month.'">' . date( 'F', mktime(0, 0, 0, $month) ). ' (' . count($theids) . ')</a></li>' ;
                        ?>
                    </h4>
            <?php  

            endforeach;?>

    <?php 

    endforeach;

    ?>

</ul>

This is the end result:

Answer 3

I would use more PHP than SQL. This way, you only need to hit the database once for your posts.

Add a new WordPress Dashboard widget.

/* Add a Dashboard widget for reports submitted. */
function mbe_add_dashboard_widget(){
    wp_add_dashboard_widget('mbe_dashboard_widget_reports', 'Reports Submitted',   'mbe_dashboard_widget_reports');
}
add_action('wp_dashboard_setup', 'mbe_add_dashboard_widget');

Setup the actual WordPress Dashboard widget for all submitted reports.

/* Reports Submitted Widget. */
function mbe_dashboard_widget_reports(){
    $data = mbe_get_reports();
    if($data){
        echo '<table style="width: 100%;">'.PHP_EOL;
        echo '<tr>'.PHP_EOL;
        echo '<td style="width: 33%;"><strong>Reports by Year</strong></td>'.PHP_EOL;
        echo '<td style="width: 33%;"><strong>Reports by Month</strong></td>'.PHP_EOL;
        echo '<td style="width: 33%;"><strong>Reports by Day</strong></td>'.PHP_EOL;
        echo '</tr>'.PHP_EOL;
        echo '<tr>'.PHP_EOL;
        echo '<td style="vertical-align: text-top;">'.mbe_reports_by('year', $data).'</td>'.PHP_EOL;// Yearly
        echo '<td style="vertical-align: text-top;">'.mbe_reports_by('month', $data).'</td>'.PHP_EOL;// Monthly
        echo '<td style="vertical-align: text-top;">'.mbe_reports_by('day', $data).'</td>'.PHP_EOL;// Daily
        echo '</tr>'.PHP_EOL;
        echo '</table>'.PHP_EOL;
    }
}

The main function to be used for fetching your initial WordPress post data for reports submitted.

/* Get all reports. */
function mbe_get_reports(){
    global $wpdb;

    /* Prevent typos later on. */
    $post_type = 'report';
    $post_status = 'publish';

    $posts = $wpdb->get_results($wpdb->prepare(
        "
        SELECT ID, post_title, post_date
        FROM {$wpdb->posts}
        WHERE `post_status` = '%s'
        AND `post_type` = '%s'
        ",
        $post_status,
        $post_type
    ));

    /* No posts. */
    if(empty($posts)){
        return false;
    }

    $post_data = array(); // Store data for later.
    for($i = 0; $i < count($posts); $i++){
        $post = $posts[$i];
        /* Separate the date from the time. */
        $post_date = explode(' ', $post->post_date);
        /* Separate the day, month, and year from each other. */
        $the_date = explode('-', $post_date[0]);
        $month = date('F', mktime(0, 0, 0, $the_date[1]));
        $day = date('l', mktime(0, 0, 0, $the_date[2]));
        /* Store post data nested in the format: year, month, day. */
        $post_data[$the_date[0]][$month][$day][] = array(
            'post_id' => $post->ID,
            'post_title' => $post->post_title,
            'post_slug' => sanitize_title($post->post_title),
            'post_permalink' => get_permalink($post->ID),
            'post_type' => $post_type,
            'post_status' => $post_status,
            'post_date' => date('l, F d, Y', strtotime(join('-', $the_date))),// Ex. Saturday, October 06, 2012
            'post_time' => date('g:i A', strtotime($post_date[1]))// Ex. 3:00 PM
        );  
    }

    /* Done with $posts, remove it. */
    unset($posts);

    /* Return the data. */
    if(!empty($post_data)){
        $return = $post_data;
    } else{
        $return = false;
    }

    return $return;

}

Sort your reports by year, month, and day, then display them in an unordered list within the table of your WordPress Dashboard widget.

/* Display report counts by year, month, or day. */
function mbe_reports_by($span, $data){
    $post_data = array();
    foreach($data as $year => $year_data){
        foreach($year_data as $month => $month_data){
            foreach($month_data as $day => $day_data){
                for($i = 0; $i < count($day_data); $i++){
                    $post = $day_data[$i];
                    if($span == 'year'){
                        $post_data[$year][] = $post;
                    } elseif($span == 'month'){
                        $post_data[$month][] = $post;
                    } elseif($span == 'day'){
                        $post_data[$day][] = $post;
                    }
                }
            }
        }
    }
    if(!empty($post_data)){
        $content = '';
        $content .= '<ul>'.PHP_EOL;
        uksort($post_data, 'mbe_date_sort');
        foreach($post_data as $time_span => $posts){
            $content .= '<li>'.$time_span.' (<a title="View posts in '.$time_span.'" href="'.admin_url('/edit.php?post_type=report', 'http').'">'.count($posts).'</a>)</li>'.PHP_EOL;
        }
        $content .= '</ul>'.PHP_EOL;
        $return = $content;
    } else{
        $content = '<p>No reports found.</p>'.PHP_EOL;  
    }
    return $content;
}

Instead of having jumbled dates in a random order, they should be sorted. We can't just sort the dates in ascending or descending order, we need to sort them in chronological order.

/* Sort dates chronologically. */
function mbe_date_sort($a, $b){
    if($a == $b){ 
        return 0; 
    } else{  
        $a = strtotime($a); 
        $b = strtotime($b); 
        if($a < $b){ 
            return -1; 
        } else{ 
            return 1; 
        } 
    } 
}

If you wanted to hyper-link all of your report counts to list all of the posts from that year, month, or day... I would advise attaching three custom taxonomies to your "report" custom post type.

You will need a custom taxonomy for "reports by year", "reports by month", and "reports by day" and they will need to be attached to your "report" custom post type. You can do this with register_taxonomy();

You would then need to fill each of your newly created taxonomies with terms. Each term would be reflect it's respective taxonomy.

• The "reports_by_year" custom taxonomy, would need to contain terms for each of the years for which a report has been generated (Ex: 2009, 2010, 2011, 2012).
• The "reports_by_month" custom taxonomy, would need to contain terms for each of the months in a year (Ex: January through December).
• The "reports_by_day" custom taxonomy, would need to contain terms for each of the days in a week (Ex: Sunday to Saturday).

Each time a report is generated, it would need to automatically set the proper terms within each custom taxonomy. This could be done using wp_set_object_terms(); and the save_post action hook. Learn more about WordPress actions.

Once that's been taken care of, you could use this as a hyper-link for the count of reports.

• Link to reports from that year:
  admin_url('/edit.php?reports_by_year='.$time_span.'&post_type=report&orderby=title&order=asc', 'http');
• Link to reports from that month:
  admin_url('/edit.php?reports_by_month='.$time_span.'&post_type=report&orderby=title&order=asc', 'http');
• Link to reports from that day:
  admin_url('/edit.php?reports_by_day='.$time_span.'&post_type=report&orderby=title&order=asc', 'http');

So when you click the count of reports, it would bring you to a table containing only the reports generated on that year, month, or day and would be sorted in ascending order by the title of the report.