Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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 how i would like it to look like.

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? :)

share|improve this question
    
Your logic is running three queries (first getting year, then from that getting month, and from that getting count). These can all be combined into a single query and round-trips to the database can be eliminated: SELECT YEAR(post_date) AS year, MONTH(post_date) AS month, COUNT(*) AS count_of FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'report' GROUP BY YEAR(post_date), MONTH(post_date); –  Justin Bell Oct 1 at 3:59

3 Answers 3

up vote 3 down vote accepted
+50

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:

enter image description here

share|improve this answer
    
Thank you very much. I knew i was forgetting something I just could not figure out what it was. I really appreciate it. Again thanks. :) –  Nicole Oct 8 '12 at 14:13

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.

share|improve this answer

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:

Custom Query end result

share|improve this answer
    
unfortunately i have tried your answer but received a database error when implementing it. I get this error Fatal 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 –  Nicole Oct 3 '12 at 21:00
    
My apologies. Try putting this before the code: global $wpdb; –  Jonathan Oct 3 '12 at 21:06
    
Also, towards the end you may want to adjust the link that it creates. I had done this snippet to create an archive of custom post types. Changing <a href="/mediaposts/'.$year.'/'.$month.'"> to <a href="/'.$year.'/'.$month.'"> should direct you to your archives page –  Jonathan Oct 3 '12 at 21:09
    
Ah haha! You sir are a genius. :) Thank you. However i am only getting one month displayed. Which is just September i also have posts in October. –  Nicole Oct 3 '12 at 21:19
    
hmmm...just for kicks. Add a new line before the global $wpdb and reset the query: <?php wp_reset_query(); ?> Just to make sure it's not being affected by another query –  Jonathan Oct 3 '12 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.