I am using the following code to output a list of items in my 'documents' Custom Post type by Year and Month.
<ul>
<?php
$post_type = 'documents';
global $wpdb;
/**/
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' ORDER BY post_date DESC");
foreach($years as $year) :
$years_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year.";" ) );
?>
<li class="year"><?php echo $year; ?> (<?php echo $years_count; ?> Posts)
<ul>
<? $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
foreach($months as $month) :
$months_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";" ) );
?>
<li class="month">>
<a href="<?php echo get_month_link($year, $month)."?post_type=".$post_type; ?>"><?php echo date( 'F', mktime(0, 0, 0, $month) );?></a> (<?php echo $months_count; ?> Posts)
<?php $theids = $wpdb->get_results("SELECT ID, post_title, post_date FROM $wpdb->posts WHERE post_type = '".$post_type."' AND post_status = 'publish' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";");
foreach ($theids as $theid):
?>
<h4 style="font-style:italic;"><li><a href="#"><?php echo $theid->post_title; ?></a></li></h4>
<?php
endforeach; ?>
</li>
<?php endforeach;?>
</ul>
</li>
<?php endforeach; ?>
</ul>
Currently this is working as expected but I am having problems trying to do the same thing for a specific category within my documents post type. How would I refine this code for my category 'cascade-campus-docs' within my 'documents' post type.
I am using the following code to output a list of items in my 'documents' Custom Post type by Year and Month.
<ul>
<?php
$post_type = 'documents';
global $wpdb;
/**/
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' ORDER BY post_date DESC");
foreach($years as $year) :
$years_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year.";" ) );
?>
<li class="year"><?php echo $year; ?> (<?php echo $years_count; ?> Posts)
<ul>
<? $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
foreach($months as $month) :
$months_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = '".$post_type."' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";" ) );
?>
<li class="month">>
<a href="<?php echo get_month_link($year, $month)."?post_type=".$post_type; ?>"><?php echo date( 'F', mktime(0, 0, 0, $month) );?></a> (<?php echo $months_count; ?> Posts)
<?php $theids = $wpdb->get_results("SELECT ID, post_title, post_date FROM $wpdb->posts WHERE post_type = '".$post_type."' AND post_status = 'publish' AND YEAR(post_date) = ".$year." and MONTH(post_date) = ".$month.";");
foreach ($theids as $theid):
?>
<h4 style="font-style:italic;"><li><a href="#"><?php echo $theid->post_title; ?></a></li></h4>
<?php
endforeach; ?>
</li>
<?php endforeach;?>
</ul>
</li>
<?php endforeach; ?>
</ul>
Currently this is working as expected but I am having problems trying to do the same thing for a specific category within my documents post type. How would I refine this code for my category 'cascade-campus-docs' within my 'documents' post type.
Thanks SickHippie.
This is what I ended up with:
<?php $args = array(
'posts_per_page' => -1,
'post_type' => 'documents',
'post_status' => 'publish',
'tax_query' => array(
array(
'taxonomy' => 'download-category',
'field' => 'slug',
'terms' => 'cascade-campus-docs'
)
)
);
$my_query = null;
$my_query = new WP_Query($args);
?>
<?php if( $my_query->have_posts() ):
while ($my_query->have_posts()) : $my_query->the_post(); ?>
<?php
$year = get_the_date('Y', '', '', FALSE);
if ($year !== $year_check) {
echo "<h2 class='year'>" . $year . "</h2>";
}
$year_check = $year;
?>
<?php the_date('F', '<p class="the_date"><span>', '</span></p>'); ?>
<p><a href="#" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
<?php endwhile;
endif;
wp_reset_query(); ?>
Don't use $wpdb, use wp_query. Not only will your code be leaner and quicker, you'll avoid issues like this. Unless you're doing something with custom tables or seriously in-depth SQL, there's no reason to do direct SQL calls on $wpdb.
So, to answer your question - rewrite it from the ground up using wp_query, see that everything you want to do is documented in the Codex, solve your problems without any hassle, and pat yourself on the back, a job well done.
I was searching for this for the last one week but found nothing. Hopefully got this query to display specific category posts only:
<?php
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.taxonomy='category') INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE $wpdb->terms.slug='blog' ORDER BY post_date DESC");
foreach($years as $year) :
?>
<li><a href="JavaScript:void()"><?php echo $year; ?></a>
<ul class="archive-sub-menu">
<? $months = $wpdb->get_col("SELECT DISTINCT MONTH(post_date)
FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post'
AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");
foreach($months as $month) :
?>
<li><a href="<?php echo get_month_link($year, $month); ?>">
<?php echo date( 'F', mktime(0, 0, 0, $month) );?></a>
</li>
<?php endforeach;?>
</ul>
</li>
<?php endforeach; ?>
wp_queryfunction: codex.wordpress/Class_Reference/WP_Query – Zach Lysobey Commented Jun 29, 2012 at 18:37