Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
360 views
in Technique[技术] by (71.8m points)

sql - Custom post type yearly/ monthly archive

I have a custom post type "news" in my Wordpress site. I am using Advanced Custom Fields plugin to add meta data to each post.

I want to create an array of news items as an archive:

[2013]
    [January] => 5
[2012]
    [January] => 20
    [February] => 10
[2011]
    [April] => 30

I managed to get this working using:

    global $wpdb;
    $news = $wpdb->get_results(
        "SELECT wp_posts.post_date, COUNT(wp_posts.ID) as count
         FROM $wpdb->posts
         WHERE
         wp_posts.post_type = 'news' AND
         wp_posts.post_status = 'publish' AND
         wp_posts.post_date <= CURDATE() AND
         wp_posts.post_date >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
         GROUP BY YEAR(wp_posts.post_date), MONTH(wp_posts.post_date)
         ORDER BY wp_posts.post_date DESC", 
         ARRAY_A);

    $archive = array();
    foreach ($news as $post):
        $year = date('Y', strtotime($post['post_date']));      
        $month = date('m', strtotime($post['post_date']));     
        $month_name = date('F', strtotime($post['post_date']));
        $post['url'] = 'NOT SURE ABOUT URL';
        $archive[$year][$month_name] = $post;
    endforeach;

I need to be able to link to specific years and months using http://example.com/2012/ and http://example.com/2012/10/.

As this involves a custom post type "news" I'm unsure how to make this happen?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

In order to do what you require, you need to modify Wordpress rewrites to be able to capture the year/month/etc post data for a custom post type.

You may do this with the following code:

/**
 * Custom post type date archives
 */

/**
 * Custom post type specific rewrite rules
 * @return wp_rewrite             Rewrite rules handled by Wordpress
 */
function cpt_rewrite_rules($wp_rewrite) {
    $rules = cpt_generate_date_archives('news', $wp_rewrite);
    $wp_rewrite->rules = $rules + $wp_rewrite->rules;
    return $wp_rewrite;
}
add_action('generate_rewrite_rules', 'cpt_rewrite_rules');

/**
 * Generate date archive rewrite rules for a given custom post type
 * @param  string $cpt        slug of the custom post type
 * @return rules              returns a set of rewrite rules for Wordpress to handle
 */
function cpt_generate_date_archives($cpt, $wp_rewrite) {
    $rules = array();

    $post_type = get_post_type_object($cpt);
    $slug_archive = $post_type->has_archive;
    if ($slug_archive === false) return $rules;
    if ($slug_archive === true) {
        $slug_archive = $post_type->name;
    }

    $dates = array(
        array(
            'rule' => "([0-9]{4})/([0-9]{1,2})/([0-9]{1,2})",
            'vars' => array('year', 'monthnum', 'day')),
        array(
            'rule' => "([0-9]{4})/([0-9]{1,2})",
            'vars' => array('year', 'monthnum')),
        array(
            'rule' => "([0-9]{4})",
            'vars' => array('year'))
        );

    foreach ($dates as $data) {
        $query = 'index.php?post_type='.$cpt;
        $rule = $slug_archive.'/'.$data['rule'];

        $i = 1;
        foreach ($data['vars'] as $var) {
            $query.= '&'.$var.'='.$wp_rewrite->preg_index($i);
            $i++;
        }

        $rules[$rule."/?$"] = $query;
        $rules[$rule."/feed/(feed|rdf|rss|rss2|atom)/?$"] = $query."&feed=".$wp_rewrite->preg_index($i);
        $rules[$rule."/(feed|rdf|rss|rss2|atom)/?$"] = $query."&feed=".$wp_rewrite->preg_index($i);
        $rules[$rule."/page/([0-9]{1,})/?$"] = $query."&paged=".$wp_rewrite->preg_index($i);
    }
    return $rules;
}

You will notice that I have hard-coded news in to the $rules = cpt_generate_date_archives('news', $wp_rewrite); portion of the code. You can change this as needed.

With this code, you should be able to navigate to http://yoursite.com/news/2013/02/ and receive an archive listing for that specific post type for that specific time.

For completeness, I will include how to generate a monthly archive widget in order to utilize this.

/**
 * Get a montlhy archive list for a custom post type
 * @param  string  $cpt  Slug of the custom post type
 * @param  boolean $echo Whether to echo the output
 * @return array         Return the output as an array to be parsed on the template level
 */
function get_cpt_archives( $cpt, $echo = false )
{
    global $wpdb; 
    $sql = $wpdb->prepare("SELECT * FROM $wpdb->posts WHERE post_type = %s AND post_status = 'publish' GROUP BY YEAR($wpdb->posts.post_date), MONTH($wpdb->posts.post_date) ORDER BY $wpdb->posts.post_date DESC", $cpt);
    $results = $wpdb->get_results($sql);

    if ( $results )
    {
        $archive = array();
        foreach ($results as $r)
        {
            $year = date('Y', strtotime( $r->post_date ) );
            $month = date('F', strtotime( $r->post_date ) );
            $month_num = date('m', strtotime( $r->post_date ) );
            $link = get_bloginfo('siteurl') . '/' . $cpt . '/' . $year . '/' . $month_num;
            $this_archive = array( 'month' => $month, 'year' => $year, 'link' => $link );
            array_push( $archive, $this_archive );
        }

        if( !$echo )
            return $archive;
        foreach( $archive as $a )
        {
            echo '<li><a href="' . $a['link'] . '">' . $a['month'] . ' ' . $a['year'] . '</a></li>';
        }
    }
    return false;
}

To use this function just supply the slug of the custom post type, ie: get_cpt_archives( 'news' ). This will return an array of unique Year/Dates/Links, ie:

Array
(
    [0] => Array
        (
            [month] => February
            [year] => 2013
            [link] => http://yoursite.com/news/2013/02
        )

    [1] => Array
        (
            [month] => January
            [year] => 2013
            [link] => http://yoursite.com/news/2013/01
        )

)

You can loop through these with a foreach and output them however you want.

Alternatively, you can use get_cpt_archives( 'news', true ) which will automatically echo each item wrapped in an <li> linking to its specific archive.

The formatting of the output is not exactly what you wanted, so you will have to tweak it a bit to get it to display in the

Year
    Month
    Month
Year
    Month

format that you require.

I hope this helps.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...