Great to hear that, although your reply will not help the next poor guy searching the forum and finding a “nevermind”…
Actually, I never used any special sql query to create an Archive Page (if we talk about the same thing!).
Are you talking about the monthly archives – which should be displayed by default using the archive.php template file of your theme? Or are we talking about a special Page Template like “archives.php” (notice the s!) which displays whatever code you put into the template?
Thread Starter
uladk
(@uladk)
I made a custom archives.php template that was a combination of wp_get_archives('type=monthly')
and wp_get_archives('type=postbypost')
so each month also showed its ‘postbypost.’
The code:
<?php
global $month, $wpdb;
$now = current_time('mysql');
$arcresults = $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM " . $wpdb->posts . " WHERE post_date <'" . $now . "' AND post_status='publish' AND post_password='' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC");
if ($arcresults) {
foreach ($arcresults as $arcresult) {
$url = get_month_link($arcresult->year, $arcresult->month);
$text = sprintf('%s %d', $month[zeroise($arcresult->month,2)], $arcresult->year);
echo get_archives_link($url, $text, '','<h5>','</h5>');
$thismonth = zeroise($arcresult->month,2);
$thisyear = $arcresult->year;
$arcresults2 = $wpdb->get_results("SELECT ID, post_date, post_title, post_excerpt, comment_status FROM " . $wpdb->posts . " WHERE post_date LIKE '$thisyear-$thismonth-%' AND post_status='publish' AND post_password='' ORDER BY post_date DESC");
if ($arcresults2) {
echo"<ul class='arc'>";
foreach ($arcresults2 as $arcresult2) {
if ($arcresult2->post_date != '0000-00-00 00:00:00') {
$url = get_permalink($arcresult2->ID);
$arc_title = $arcresult2->post_title;
$excerpt_arc = $arcresult2->post_excerpt;
if ($arc_title) $text = strip_tags($arc_title);
else $text = $arcresult2->ID;
echo "<p class='trig trigger'>".get_archives_link($url, $text, '');
$comments = mysql_query("SELECT * FROM " . $wpdb->comments . " WHERE comment_post_ID=" . $arcresult2->ID);
$comments_count = mysql_num_rows($comments);
if ($arcresult2->comment_status == "open" OR $comments_count > 0) echo ' ['.$comments_count.']';
echo "
";
echo"<div class='excerpt'>";
echo"$excerpt_arc
";
echo"<p class='viewarc' style='text-align: right;'><a href=$url>View article</a>
";
echo"</div>";
}
}
echo"</ul>";
}
}
}
?>
The result:
http://www.designlegion.com/rave/archives/