Support » Fixing WordPress » Custom select query for a category with date seperation

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

    2009
    January

    • Post 7
    • Post 6
    • Post 5

    2008
    December

    • Post 4
    • Post 3

    November

    • 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; ?>
    
    <?php
    $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?

    Thnx!

Viewing 1 replies (of 1 total)
Viewing 1 replies (of 1 total)
  • The topic ‘Custom select query for a category with date seperation’ is closed to new replies.