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!