Sort X categories by last update and show image

Question

I want to display X categories, and sort them by last update.

I also want to grab the image of the latest post with the category.

<img src="<?php echo get_image('article_image',1,1,0,NULL,$res75); ?>" />

So, the category list will be displayed by images, with the corresponding category-name underneath.

Sorted by last update.

How would I go about doing that?

Answer 1

Here's one solution:

global $wpdb;

$cat_array = $wpdb->get_results( "
    SELECT terms.*, posts.ID as post_ID
    FROM wp_terms terms 
    JOIN wp_term_taxonomy term_taxonomy 
        ON terms.term_id = term_taxonomy.term_id
    JOIN wp_term_relationships term_relationships 
        ON ( term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
            AND term_taxonomy.taxonomy = 'category' )
    JOIN wp_posts posts 
        ON ( posts.ID = term_relationships.object_id 
            AND posts.post_type='post'
            AND posts.post_status='publish' )
    GROUP BY terms.term_id
    ORDER BY posts.post_modified_gmt DESC" );

foreach ($cat_array as $cat) {

    $category = get_term_by( 'ID', $cat->term_id, 'category');
    $post = get_post( $cat->post_ID );

    echo '<a href="' . esc_attr(get_term_link($category, 'category')) . '" title="' . sprintf( __( "View all posts in %s" ), $category->name ) . '" ' . '>';
    echo get_the_post_thumbnail( $cat->post_ID, 'post-thumbnail' );
    echo $category->name .': '.get_the_title( $post->ID ).'</a>'.'<br />';

}

The query returns the categories in the order you want, sorted by most recent post, along with the post ID of the most recent post in that category, which you can then use to get the post thumbnail or whatever other data you want from it (I also echoed the post title in my example, just to show how it can be done).

Answer 2

Ok, so I figured out half of the solution.

This code lists categories by last update;

        <?php 
        $cat_array = array();
        $args=array(
          'post_type' => 'post',
          'post_status' => 'publish',
          'posts_per_page' => 10,
          'caller_get_posts'=> 1
          );
        $my_query = null;
        $my_query = new WP_Query($args);
        if( $my_query->have_posts() ) {
          while ($my_query->have_posts()) : $my_query->the_post();
            $cat_args=array('orderby' => 'none');
            $cats = wp_get_post_terms( $post->ID , 'category', $cat_args);
            foreach($cats as $cat) {
              $cat_array[$cat->term_id] = $cat->term_id;
            }
          endwhile;
        }
        if ($cat_array) {
          foreach($cat_array as $cat) {
            $category = get_term_by('ID',$cat, 'category');
            echo '<a href="' . esc_attr(get_term_link($category, 'category')) . '" title="' . sprintf( __( "View all posts in %s" ), $category->name ) . '" ' . '>' . $category->name.'</a>'.'<br />';
          }
        }
        wp_reset_query();
    ?>

However, I'm not sure how I can make it display the corresponding image to the category.

With category X, it should display the image belonging to the latest post in that category.

Anyone can help with this? Thanks!