Archives by Date and Category

Thanks! Very thoughtful and generous!

It sounds great. I would like to implement it, but I’m getting this error. I was wondering if I could get some help. Thanks a lot!

WordPress database error: [You have an error in your SQL syntax near ‘AND post_date != ‘0000-00-00 00:00:00’ AND post_status = ‘publish’ GROUP BY YEAR’ at line 1]
SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM wp_posts, wp_post2cat WHERE post_date < ‘2006-06-18 17:37:11’ AND category_id = AND post_date != ‘0000-00-00 00:00:00’ AND post_status = ‘publish’ GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC

I changed this line of code that goes on the sidebar from:

$catmnthquery=(“type=catmonthly&mcat=” .$cat);

to:

$catmnthquery=($cat);

It seem to work good.

Ok, that didn’t work. I’m still getting that error. I’m going to post what I did why my code as instructed above, what is wrong with this code???
[long code moderated]

Don’t post long code here, please!
It is difficult to read and nobody really bothers to take a look at it. You can use https://pastebin.com or similar services – and post back with the URI.
For posting small code snippets, please read carefully the instructions here below the text input area: the “backticks” usually are located on top-left of your keyboard, where ~ [tilde] is.

When I was looking for a similar WP feature I stumbled upon this post and I might have a solution for you bex_oo ??

Firstly, adding $mcat = false to the list of parameters in the get_archives function will get you half way there. get_archives doesn’t know the value of mcat if you don’t pass it along.
It should look something like this:

function get_archives($type='', $limit='', format='html', $before = '', $after = '', $show_post_count = false, $mcat = false) {

Secondly there seems to be no join between the two tables that are being used in the get_archives SQL code part rrajakumar posted. To get the desired result you should also add id = post_id to the where clause, like this:

$arcresults = $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM $wpdb->posts, $wpdb->post2cat WHERE id = post_id AND post_date < '$now' AND category_id = $mcat AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" . $limit);

Hope this helps!

This was easily implemented and worked very well, thanks to rrajakumar and yper for the update!

I have a question about conditional statements using catmonthly and the catmonthly archives.

I used this solution to run the “news” and “events” sections of a website while keeping their archives separate. (It’s behind a vpn or I would link to it here.) I’m using a conditional statement to test for category type and write custom sidebar navigation.

If possible, I would like to make the category have a different class if the user is on the main category page, and each archive have a different class if the user is on that specific archive page. The docs on conditional statements did not address it and I read through the dynamic menu highlighting tutorial.

Is there a way to test for main category page and each archive month in turn?

Here is the psuedo code – I have left irrelevant parts out and my PHP may be seriously flawed. I can figure out the details if you point me in the right direction.
<php /* If this is news */ if (category(2)) { ?>
<h4>News Header</h4>
<ul>
<?php /* If this is news frontpage */ if (is_catmonthly) }{ ?>
<li id="current">Latest News
<?php } else { ?>

<li>Latest News</li>
<?php } ?>
</ul>
<h4>News Archives Header</h4>
<ul>
/* How do I test for active archive title here? Is it even possible? */
<li id="subnavitem"><?php $catmnthquery=("type=catmonthly&mcat=" .$cat); wp_get_archives($catmnthquery); ?>
</ul><?php } else - look for other category { ?>
write other category's code, etc.
<?php } ?>`

I see that I have to define a new conditional statement for catmonthly, “is_catmonthly,” and that there’s a place to do so in functions.php. How would I go about this and what else do I have to do to make it work?

I’m a designer, not a programmer, and am doing my best.

Thanks in advance for your help, and I’m sorry if this isn’t the right place to ask.

how would one use this code to list the most recent posts from by category rather than the monthly archives?

I hope this gets rolled into a future release of WordPress… I was really surprised to find this functionality wasn’t possible with the core release, and really glad to find somebody had already solved this problem!

The code (and subsequent tweaks) seem to work well, except for one line near the end of the new elseif block in get_archives():

$url = get_month_link($arcresult->year, $arcresult->month) ."&cat=" .$mcat;

It might be peculiar to the way I’ve organized my pages, but I found that I had to change "&cat=" to "?cat=" to get the archive links to actually work.

usonian, that’s probably because we’re using permalinks. Since our urls are like so — /2006/08/ — using &cat won’t work. You’ve got to use ?cat.

Now if the developers would just:

1) role this functionality into WordPress and
2) allow for permalink structures like /categories/catname/2006/08

we’d never have to worry about this again…

there is actually a more simple solution to this. if you use /%category%/%year%/%monthnum%/%day%/%postname%/ as your permalink structure you get category archives by year and month ??

I’m also getting an SQL error for this:

...the right syntax to use near 'AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR' at line 1]...

my get archives() and sql string was updated to yper’s suggestion.

The code on my category archive page looks like:

<?php $catmnthquery=($cat); ?> <?php wp_get_archives('$catmnthquery'); ?>

Any ideas? Thanks!

nevermind – got it ??

I’ve followed all rrajakumar, yper and usonian’s instructions, but I’m still getting the

…the right syntax to use near ‘AND post_date != ‘0000-00-00 00:00:00’ AND post_status = ‘publish’ GROUP BY YEAR’ at line 1]…

error. If bcreighton or anyone else can help, I’d be most grateful.

You can see my template_functions_general.php code at https://pastebin.ca/321599

My search query is:

<?php $catmnthquery=(‘type=catmonthly&mcat=1’); wp_get_archives($catmnthquery); ?>

Intended to bring up a list of months which have archives for Category 1… The page it’s operating on is at https://www.3ammagazine.com/3am/buzzwords/ – please note, this page is not officially live yet.

All help gratefully recieved.

I’ve modified the code above so it works with the changed code of the 2.1 branch. The additional parameter is nicely received, the “elseif catmonthly” is executed and then… I’m getting not getting an SQL error, but an empty result, I’ve tried a couple of versions, this one being the last, but it’s consistently just empty. What’s wrong? Could someone please tell my what the problem with this statement is? Thanks so much!

$arcresults = $wpdb->get_results("SELECT DISTINCT YEAR(post_date) ASyear, MONTH(post_date) ASmonth, count(ID) as posts FROM $wpdb->posts, $wpdb->post2cat WHERE $wpdb->posts.post_type='post' AND $wpdb->posts.ID = $wpdb->post2cat.post_id AND $wpdb->posts.post_date < '$now' AND $wpdb->post2cat.category_id = '$mcat' AND $wpdb->posts.post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC" . $limit);