Custom select query for a category with date seperation
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; ?> <?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?
- The topic ‘Custom select query for a category with date seperation’ is closed to new replies.