The queries have been updated for WordPress 2.1.

I’ve been working on my archives page on my personal blog for some time now, and I thought I’d share some custom MySQL queries in order to allow for some custom archives tweaking in your WordPress blog. I have three pages set up, with one showing dates, one showing categories, and one showing all of the entries. All three code snippets require a custom WordPress page, which is outside the scope of this article.

An additional query has been added that shows all of the categories, but has all the posts within that category shown as well.

Showing the Dates

I wanted to have a page that listed all of the dates on my blog. However, I also wanted the number of posts to be included in the link. This cannot be achieved using the standard get_archives template tag from the WordPress Codex. A custom query was needed. Shown below is the code I used followed by a brief description.

<ul class="archive-list">
							<?php 
$months = $wpdb->get_results("SELECT DISTINCT MONTH( post_date ) AS month , YEAR( post_date ) AS year, COUNT( id ) as post_count FROM $wpdb->posts WHERE post_status = 'publish' and post_date <= now( ) and post_type = 'post' GROUP BY month , year ORDER BY post_date DESC");
foreach($months as $month) : ?>
<li><a href="<?php bloginfo('url') ?>/<?php echo $month->year; ?>/<?php echo date("m", mktime(0, 0, 0, $month->month, 1, $month->year)) ?>"><?php echo date("F", mktime(0, 0, 0, $month->month, 1, $month->year)) ?> <?php echo $month->year ?> - <span class="archive-list-small"><?php echo $month->post_count; if ($month->post_count > 1 ) { echo " posts"; } else { echo " post"; }?></span></a></li>
<?php endforeach; ?>
							</ul>

The query grabs the month and year from a post’s date. The query then groups the results by month and year, and counts the number of ID matches for that time period.

After the query has been run, I embed the post count inside the link for the date. If you plan on using the above snippet, you may need to change a few things depending on how your permalink structure is setup. You can see the result in the screenshot below:

Showing the Categories

The next thing I wanted to have on my archives page was a page showing the categories I wanted with the number of posts embedded within the link (much like the dates example above). Shown below is the code I used followed by a brief description.

<ul class="archive-list">
 						<?php 
						$query = "SELECT cat_ID, cat_name, category_count, category_nicename FROM $wpdb->categories WHERE cat_ID NOT IN ( 10, 1, 22, 23 ) AND category_parent =0 ORDER BY cat_name ASC";
						if ( $cats = $wpdb->get_results($query) ) {
						foreach ($cats as $cat) {
						?>
							<li><a href="<?php bloginfo('url') ?>/category/<?php echo $cat->category_nicename; ?>/"><?php echo $cat->cat_name; ?> - <span class="archive-list-small"><?php echo $cat->category_count; ?> posts</span></a></li>
							<?php
							}
						} ?>
						</ul>

The query grabs all the parent categories and excludes certain ones by the category ID number. Once again, you may need to change the link structure to match your permalink structure. Shown below is a screenshot of the result:

Showing All Entries

I wanted an archive page to display every entry in my blog, but I wanted a little more control than what wp_get_archives('type=postbypost') gave me. Shown below is the code I used to retrieve all of my entries:

<ul class="archive-list">
 						<?php 
						$query = "SELECT post_title, MONTH( post_date ) AS month ,  YEAR( post_date ) AS year, DAY( post_date ) AS day , id FROM $wpdb->posts WHERE post_status = 'publish' AND post_date <= now( ) and post_type = 'post' ORDER BY post_date DESC";
						if ( $posts = $wpdb->get_results($query) ) {
						foreach ($posts as $post) {
										
						?> 
						<li><a href="<?php echo get_permalink($post->id)?>"><?php echo $post->post_title ?> - <span class="archive-list-small"><?php echo date("F jS, Y",mktime(0, 0, 0, $post->month, $post->day, $post->year)) ?></span></a></li>
						<?php
						}
						}
						?>												
  </ul>

The query grabs all posts (including the date) and orders them by the post date. I wanted to embed the date within the link so that people would know when I posted a particular entry. Shown below is a screenshot of the result:

Showing Categories and Posts

One of our readers requested a query that allowed the display of categories, but also the posts within that category. We at Devlounge try to serve are readers as much as possible, so I have some up with a solution. Shown below is the code.

		<?php 
						$query = "select c.cat_ID, c.cat_name, c.category_nicename, c.category_count, p.ID, p.post_title, p.post_date, MONTH( p.post_date ) AS month ,  YEAR( p.post_date ) AS year, DAY( p.post_date ) AS day from $wpdb->post2cat pc, $wpdb->categories c, $wpdb->posts p where  c.cat_ID NOT IN (1) and c.category_parent = 0 and pc.category_id = c.cat_ID and pc.post_id = p.ID and p.post_status = 'publish' AND p.post_date <= now( ) order by c.cat_name asc, p.post_date desc";
						$oldCat = "";
						$currentCat = "";
						if ( $cat_posts = $wpdb->get_results($query) ) {
						foreach ($cat_posts as $cat_post) {
							$currentCat = $cat_post->category_nicename;
							if ($currentCat != $oldCat && $oldCat != "") { echo "</ul>"; }
							if ($currentCat != $oldCat) {
							?>
							<h4><a href="<?php bloginfo('url') ?>/category/<?php echo $cat_post->category_nicename; ?>/"><?php echo $cat_post->cat_name; ?> - <span class="archive-list-small"><?php echo $cat_post->category_count; ?> posts</span></a></h4>
							<ul>
							<?php
							} ?>
							<li><a href="<?php echo get_permalink($cat_post->ID)?>"><?php echo $cat_post->post_title ?> - <span class="archive-list-small"><?php echo date("F jS, Y",mktime(0, 0, 0, $cat_post->month, $cat_post->day, $cat_post->year)) ?></span></a></li>
							<?php
							$oldCat = $cat_post->category_nicename;
							}	//end foreach
						}	
						
						?>
						</ul>

The query grabs all categories and posts. The code then displays the category in an H4 tag and lists all of the posts the category contains beneath it. As the reader requested, the posts are organized by date. Below is an “un-styled” screenshot of the result:

There are two things to note about this query. The “c.cat_ID NOT IN (1, 2, etc...)” can be taken out of the query if there aren’t any category IDs to exclude. The “c.category_parent = 0” can also be taken out if you wish to show all categories and not just parent categories.

Hopefully you can use the above queries as a launching pad for your own WordPress archives page. If you need any assistance, please leave a comment.