WordPress.org

Ready to get started?Download WordPress

Forums

Archives by Date and Category (17 posts)

  1. rrajakumar
    Member
    Posted 7 years ago #

    I've seen a few people looking for a solution to the problem of sorting posts by both month and category, and I had a similar need. I wanted our blog set up so that when you are looking at a single category, the monthly archive section in the sidebar would be filtered for that category alone, and return a page of results for that month and category. I have pretty much got it working and thought I'd share what I did:

    First, you need to create a new 'type' for wp_get_archives, which has a variable $mcat that gets the value of whatever category you are currently looking at. To do that, you need to edit /wp_includes/template_functions_general.php:

    Replace the existing wp_get_archives function with:
    function wp_get_archives($args = '') {
    parse_str($args, $r);
    if ( !isset($r['type']) )
    $r['type'] = '';
    if ( !isset($r['limit']) )
    $r['limit'] = '';
    if ( !isset($r['format']) )
    $r['format'] = 'html';
    if ( !isset($r['before']) )
    $r['before'] = '';
    if ( !isset($r['after']) )
    $r['after'] = '';
    if ( !isset($r['show_post_count']) )
    $r['show_post_count'] = false;
    if ( !isset($r['mcat']) )
    $r['mcat'] = false;

    get_archives($r['type'], $r['limit'], $r['format'], $r['before'], $r['after'], $r['show_post_count'], $r['mcat']);
    }

    AND insert the following elseif into the list of types in the get_archives function:

    elseif ( 'catmonthly' == $type ) {
    $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 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);
    if ( $arcresults ) {
    $afterafter = $after;
    foreach ( $arcresults as $arcresult ) {
    $url = get_month_link($arcresult->year, $arcresult->month) ."&cat=" .$mcat;
    if ( $show_post_count ) {
    $text = sprintf('%s %d', $month[zeroise($arcresult->month,2)], $arcresult->year);
    $after = '&nbsp;('.$arcresult->posts.')' . $afterafter;
    } else {
    $text = sprintf('%s %d', $month[zeroise($arcresult->month,2)], $arcresult->year);
    }
    echo get_archives_link($url, $text, $format, $before, $after);
    }
    }
    }

    The important parts are the database query, which incorporates the new criterion, and the value that is assigned to $url, which now adds the category filter to the URL of the results page.

    Next, you need to modify sidebar.php:

    Replace:

    <li><h2>Archives</h2>
    <ul>
    <?php wp_get_archives('type=monthly'); ?>
    </ul>
    </li>

    with:

    <?php /* Whether this is a category archive */ if (is_category()) { ?>

    <li><h2>Archives for <?php single_cat_title(''); ?></h2>
    <ul>
    <?php $catmnthquery=("type=catmonthly&mcat=" .$cat); wp_get_archives($catmnthquery); ?>
    </ul>
    </li>
    <?php } else { ?>

    <li><h2>Archives</h2>
    <ul>
    <?php wp_get_archives('type=monthly'); ?>
    </ul>
    </li>
    <?php } ?>

    Now, if you are browsing a category, the 'Archives' heading in the sidebar includes the name of the category, the results (months) listed should be for that category only, and the links should return posts filtered by both criteria.

    I'm new to PHP/MySQL, so I'm sure there's a cleaner way to do all that, but I thought it might be useful.

  2. petit
    Member
    Posted 7 years ago #

    Thanks! Very thoughtful and generous!

  3. bex_00
    Member
    Posted 7 years ago #

    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

  4. bex_00
    Member
    Posted 7 years ago #

    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.

  5. bex_00
    Member
    Posted 7 years ago #

    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]

  6. moshu
    Member
    Posted 7 years ago #

    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 http://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.

  7. yper
    Member
    Posted 7 years ago #

    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!

  8. katiecy
    Member
    Posted 7 years ago #

    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.

  9. amep
    Member
    Posted 7 years ago #

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

  10. usonian
    Member
    Posted 7 years ago #

    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.

  11. jayp
    Member
    Posted 7 years ago #

    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...

  12. dtailed
    Member
    Posted 7 years ago #

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

  13. bcreighton
    Member
    Posted 7 years ago #

    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!

  14. bcreighton
    Member
    Posted 7 years ago #

    nevermind - got it :D

  15. stml
    Member
    Posted 7 years ago #

    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 http://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 http://www.3ammagazine.com/3am/buzzwords/ - please note, this page is not officially live yet.

    All help gratefully recieved.

  16. youngmicroserf
    Member
    Posted 7 years ago #

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

  17. youngmicroserf
    Member
    Posted 7 years ago #

    OK. For 2.1:

    a) don't replace wp_get_archives with the function above. That won't work. Just add

    ,'mcat' => false after the currently last element in the "defaults" array.

    Then add the "elseif code from above, but modify the SQL statement - this is the query that works.

    $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.ID = $wpdb->post2cat.post_id AND $wpdb->post2cat.category_id = '$mcat' AND $wpdb->posts.post_type = 'post' AND $wpdb->posts.post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC".$limit);

    The template command as above.

    Remember to note the changes you made because you'll have to adjust the core file after each update.

Topic Closed

This topic has been closed to new replies.

About this Topic