• Home
  • About
  • Contact
  • Advertise

Devlounge

Design, Develop, and Grow

  • Home
  • Code
  • Design
  • Design Focus
  • Interviews
  • Publishing
  • Strategy
  • Webapps
  • Extras
You are here: Home / Code / Custom Archive Queries for WordPress

Custom Archive Queries for WordPress

December 16, 2006 By Ronald Huereca

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.

[php]

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

  • month, 1, $month->year)) ?>”>month, 1, $month->year)) ?> year ?> – post_count; if ($month->post_count > 1 ) { echo ” posts”; } else { echo ” post”; }?>

[/php]

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.

[php]

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

  • category_count; ?> posts

[/php]

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:

[php]

    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) {

    ?>

  • month, $post->day, $post->year)) ?>

[/php]

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]
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 “

“; }
if ($currentCat != $oldCat) {
?>

category_count; ?> posts

  • month, $cat_post->day, $cat_post->year)) ?>
  • category_nicename;
    } //end foreach
    }

    ?>

[/php]

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.

Filed Under: Code Tagged With: archives, Articles, custom-archives, Design, development, devlounge, PHP, wordpress

Code & Tutorials

Which Front-End Development Languages Will Grow in 2017?

Which Front-End Development Languages Will Grow in 2017?

Your Guide to Leveraging APIs as a Developer

Your Guide to Leveraging APIs as a Developer

Bitcoin Processing Website Integration For Web Developers

Bitcoin Processing Website Integration For Web Developers

Website Security For 2016 That All Developers Need To Know

Website Security For 2016 That All Developers Need To Know

5 Reasons You Need to Be Using jQuery

5 Reasons You Need to Be Using jQuery

About Devlounge

Want to read more about Devlounge, or maybe you want to contact us, or even advertise? Oh, and don't forget to subscribe to updates!

The Best of Devlounge

Going Meta With Circuit-Inspired Design

Going Meta With Circuit-Inspired Design

Search

Copyright © 2018 Devlounge · Log in