Feature
Post

Category
Code


Custom Archive Queries for WordPress

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:

All Dates WordPress Archives Screenshot

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:

Categories WordPress Archives Screenshot

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:

All Entries WordPress Archives Screenshot

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:

Categories and Posts WordPress Archives Screenshot

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.


  1. By Biscuitrat posted on December 16, 2006 at 12:19 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    That’s beautiful! I wish MT could do that with the same grace that WordPress can. I’ve been trying to find something similar for MT for about two months now, with no luck. Do you think the same method (with different variables, of course) could be used for MT?

    But truly, thanks for showing all the varieties of archives! Very cool :)

  2. By ronalfy posted on December 16, 2006 at 1:06 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Biscuitrat,

    Paul Boag has MT and he does something very similar with his archives pages. I’ll try to give him an e-mail and see what he used.

    Update: E-mail was sent, but an out-of-office reply was sent back. Perhaps there are other Movable Type users that can chime in on this discussion?

  3. By Joe posted on December 16, 2006 at 2:27 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    In the words of Borat, “VERY nice…very NICE.

  4. By shaun posted on January 8, 2007 at 5:25 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    hey man, great work!

    Is there an easy way to combine the code to create a list by category and post titles by date?

  5. By ronalfy posted on January 8, 2007 at 6:26 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    @shaun,

    I’m not sure I fully understand your question.

    Are you wanting some code that will list the categories, but under each category, have all the posts within that category organized by date?

  6. By shaun posted on January 9, 2007 at 10:13 am
    Want an avatar? Get a gravatar! • You can link to this comment

    Sorry I tend to mumble when I type.

    That’s exactly what I’m looking for.

    Thanks!
    Shaun

  7. By Ronalfy posted on January 11, 2007 at 12:29 am
    Want an avatar? Get a gravatar! • You can link to this comment

    @shaun,

    When I get around to it, I’ll either write another post or append the code to this one. I’ll try to get to it this weekend.

    Take care.

  8. By shaun posted on January 11, 2007 at 10:34 am
    Want an avatar? Get a gravatar! • You can link to this comment

    awesome, thanks again!

  9. By Ronalfy posted on January 15, 2007 at 12:15 am
    Want an avatar? Get a gravatar! • You can link to this comment

    Shaun found a small bug in the Categories/Posts code above. On line 15, $cat_post->id should be $cat_post->ID.

  10. By Simon Bailey posted on February 28, 2007 at 6:10 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Deepest apologies.. you’re second example does just that .. great work – I love you forever

  11. By Arun posted on May 14, 2007 at 12:24 am
    Want an avatar? Get a gravatar! • You can link to this comment

    I get this error:

    WordPress database error: [You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘( p.post_date ) AS day FROM wp_post2cat pc, wp_categories c, wp]

    I’m using WordPress v2.1.2. Any ideas?

  12. By Ronald Huereca posted on May 14, 2007 at 5:26 am
    Want an avatar? Get a gravatar! • You can link to this comment

    Arun,

    Did you use the SQL string as is, or did you make some modifications?

    You can also try downloading a plugin that does the same thing that shows your categories and posts.

  13. By Edward posted on January 2, 2008 at 4:22 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Well done! Useful, concise, to the point. I’m already using one of your examples.

  14. By abietto posted on March 30, 2008 at 6:43 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Is there a way to combine these custom mysql queries to have a monthly archive where only certain categories of news are extracted and listed from the database? So, if there are news in the “forbidden” categories, there wouldn’t be any links in the archive? I guess you have to combine the first query with the “where cat_id not in” condition, but I’m not sure how to do that…

  15. By Alicia posted on April 8, 2008 at 11:21 am
    Want an avatar? Get a gravatar! • You can link to this comment

    Great article, too bad some examples (like category and posts) are outdated due to WordPress’ database restructuring.

  16. By Kris posted on September 1, 2008 at 1:35 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Hi! Found this article and thought it was great. I was using it just a few hours ago, and it was working fine until I tried to make it so it would stop duplicating private entries that were being shown through the plugin PrivatePost.

    I got rid of all the code and went back to the original one provided here, and now it’s not showing up any entries. Any idea as to why?

  17. By Kris posted on September 1, 2008 at 1:45 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    I deleted the plugins and restored my database, it’s now showing entries that were published prior to September 1st (I had an article scheduled to publish today). Not sure what the problem is…

  18. By reliable uk reseller hosting posted on September 2, 2008 at 12:58 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    I think some of these example are a bit outdated.

  19. By Robin posted on July 2, 2009 at 2:41 am
    Want an avatar? Get a gravatar! • You can link to this comment

    What code would you add to only list the first 10 posts in your archive when using the code above for Showing All Entries

  20. By Charlotte posted on July 27, 2009 at 8:29 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    I saw something about this on TV last night. Might be up on youtube already was on channel 10.

  21. TrackbackKokopelli | The Weblog of Simon Bailey » Blog Archive » Wordpress Archives - CustomisingPersonalizando seu Arquivo: Custom Archive Queries - Labpresskristopherlouie/blog » It’s Official