Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
I think you should be able to achieve this all using the built in wp_query function: codex.wordpress.org/Class_Reference/WP_Query –  Zach L Jun 29 '12 at 18:37

3 Answers 3

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.

share|improve this answer
1  
seconded. Check out the posts_where filter on the wp_query codex page. Very helpful for complicated queries –  Zach L Jun 29 '12 at 19:22
up vote 1 down vote accepted

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;
    }
wp_reset_query();  ?>
share|improve this answer

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; ?>
share|improve this answer
    
How does this work? A commented block of code is of much greater use than just the code alone. –  t f Jan 1 at 15:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.