Custom select query for a category with date seperation (2 posts)

  1. nielskreijveld
    Posted 7 years ago #

    Hello people,

    I've been messing around with this for a while now. I want to create a list of all the posts on the category page. Only posts from that category should be shown, and also they should be seperated by year and month. So I'll get a list like this:


    • Post 7
    • Post 6
    • Post 5


    • Post 4
    • Post 3


    • Post 2
    • Post 1

    What I've got so far:

    $aCat = get_category_by_path(get_query_var('category_name'),false);
    $iCurrentCatID = $aCat->cat_ID;
    $qYears = "SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC";
    $years = $wpdb->get_col($qYears);
    foreach($years as $year) : ?>
    <p"><?php echo $year; ?>
    $qMonths = "SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE YEAR(post_date) = $year AND post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC";
    $months = $wpdb->get_col($qMonths);
    foreach($months as $month) :
    $querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->term_relationship wpostcategories
    WHERE wposts.ID = wpostcategories.object_id
    AND wposts.YEAR(post_date) = $year
    AND wpostcategories.term_taxonomy_id = $iCurrentCatID
    AND wposts.MONTH(post_date) = $month
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    ORDER BY wposts.post_date DESC";
    $pageposts = $wpdb->get_results($querystr, OBJECT); ?>
    <?php if ($pageposts): ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>
    Some content
    <?php endforeach; ?>
    <php endif; ?>
    <?php endforeach; ?>
    <?php endforeach; ?>

    This code doesn't work, but if it did it would also mean that month names would be shown if that month contains posts from ANY category, not just the one I'm viewing right now. Ideas anyone?


  2. jrgd
    Posted 6 years ago #

    maybe a bit late but you might want to check this plugin:

Topic Closed

This topic has been closed to new replies.

About this Topic