How to list archive years per category in wordpress

This post will teach you how to list down archive years per category in your wordpress site. The script below is using SQL statement that will query from your wordpress table and the tables involve are: posts, term_relationships, term_taxonomy, and terms.

"SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.taxonomy='category') INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE $wpdb->terms.slug='<your category slug here>' ORDER BY post_date DESC"

To get the archive years per category slug, you just need to supply it below.

.... WHERE $wpdb->terms.slug='<your category slug here>' ORDER BY post_date DESC"); 

If you want to select using your category name, then change the condition above with name field $wpdb->terms.name.

.... WHERE $wpdb->terms.name='<your category name here>' ORDER BY post_date DESC"); 

<ul>
<?php

$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.taxonomy='category') INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE $wpdb->terms.slug='<your category slug here>' ORDER BY post_date DESC");               

foreach($years as $year) : ?>
        <li><a href="<?php echo get_year_link($year); ?>"><?php echo $year;?></a></li>                          
<?php endforeach; ?>
</ul> 

Please leave a comment if you like this post. Happy coding!!

Get the blog information from your wordpress site

We have a lot of ways to get the blog information from our wordpress site but below are the 2 functions that we commonly use to get it. (1) bloginfo() and (2) get_blogingo().

BLOGINFO() - This function will display the information about your blog, this is mostly gathered from the information you supplied in your blog settings. It can be use within your wordpress template. This function always print the result to the browser. If you need to get the actual value, you should use get_bloginfo() below.

Usage: bloginfo('<parameter>'); 
Parameters:

• name - the title set for the blog.
  
• description - the description set for the blog.
• admin_email - the email address of administrator of the blog.
• url - the url of the blog.
• wpurl - the url of the wordpress blog. this is usually the same as "url" parameter.
• stylesheet_directory - the directory of the stylesheet of the blog. this is usually the same as the theme directory that the blog was activated.
• stylesheet_url - the url of the stylesheet of the blog. depends also on the activated theme that the blog was activated.
• template_directory - the theme directory that the blog was activated.
• template_url - the url of the theme directory that the blog was activated.
• atom_url - the url of the atom feed.
• rss2_url - the url of the rss feed. this is the latest version of rss feed. example: http://example.com/myblog/feed
• rss_url - the url of the rss feed. this is the old version of rss feed. example: http://example.com/myblog/feed/rss
• pingback_url - the url of xmlrpc. example: http://example.com/myblog/wp/xmlrpc.php
• rdf_url - the url of rdf feed. example: http://example.com/myblog/feed/rdf
• comments_atom_url - the url o
• f comments atom feed. example: http://example.com/myblog/comments/feed/atom
• comments_rss2_url - the url of comments rss feed. example: http://example.com/myblog/comments/feed
• charset - the meta tag character set of your blog. this is usually in UTF-8 to support most of the characters.
• html_type - the html type of your blog. usually in "text/html" type.
• language - the main language of your blog. by default set to "en-US".
• version - the version of your wordpress blog.
• text_direction - don't know about this but this is usually set to "ltr". 

GET_BLOGINFO() - This function will return the information about the blog, the information here are mostly gathered from the information you supplied in your blog settings. It can be use within your wordpress template. This function will only return the results and if you need to print the result you need to either echo the result or use bloginfo() function above.

Usage: echo get_bloginfo('<parameter>'); or $info = get_bloginfo('<parameter>'); 

Parameters: (parameters are the same as above)


Hope this helps. Happy coding with wordpress!!