wordpress sql posts query won't display the latest post in a specific category

Question

Hi I tried to display 5 posts titles from a specific category also show the date, but it won't display the most recent one that just published, I had to change the recent post date 2 months so it'd show up ..

I used in_category to filter the post does in_category work with sql query ??

Thanks !

<ul id="work_items">

<?php
$max_loop=0; 
$month = array('','January', 'Febuary', 'March', 'April', 'May', 'June', 'July',     
'August', 'September', 'October', 'November', 'December');

$work_items = $wpdb->get_results("SELECT YEAR( post_date ) AS year, MONTH( post_date )    
as month, post_title as title, ID as post_id, COUNT( id ) as post_count FROM $wpdb- 
>posts WHERE post_status = 'publish' and post_type = 'post' GROUP BY year ORDER BY 
post_date DESC");

foreach($work_items as $work_item) {

if(in_category("photography",$work_item->post_id)){
echo "<li>";
echo "<a href='#' onClick='title_to_contents(".json_encode($work_item->title).")'>";
echo $work_item->title;
echo "<span class='project_date'>".$work_item->year." / ".$month[$work_item-
    >month]."</span>";
echo "</a>";
echo "</li>";
$max_loop++;
if($max_loop==4){break;}
}
 }
?>

</ul>

Answer 1

I'd suggest using the WP_Query() class for this one. Hopefully the below will help you -

<?php
$args = array(
    'category_name' => 'photography',
    'post_status' => 'publish',
    'post_type' => 'post',
    'posts_per_page' => 5
)
$work_items = new WP_Query($args);

if($work_items->have_posts()) : while($work_items->have_posts()) : $work_items->the_post()

        echo "<li>"; 
        echo "<a href='#' onClick='title_to_contents(".json_encode(get_the_title()).")'>";
        echo get_the_title(); echo "<span class='project_date'>".get_the_time('Y')." / ".get_the_time('M')."</span>";
        echo "</a>";
        echo "</li>";

    endwhile;
endif;
wp_reset_query();
?>

Answer 2

I played around with it a bit I realised that it's my COUNT(id) and GROUP by year request is grouping all my data, I had only access to the first few datas

after removing them, this works too...

<?php
// declare what category you wanna get here
$cat_name= "project"; 

$max_loop=0;
$month = array('','January', 'Febuary', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
$work_items= $wpdb->get_results("SELECT post_title as title, ID as post_id, YEAR(post_date) as y, MONTH(post_date) as month
                                                   FROM $wpdb->posts
                                                   WHERE post_status = 'publish' AND post_type = 'post'
                                                   ORDER BY post_date DESC");

foreach($work_items as $work_item) {
    if(in_category($cat_name,$work_item->post_id)){
    echo "<li>";
    echo "<a href='#' onClick='title_to_contents(".json_encode($work_item->title).",".json_encode($cat_name).")'>";
    echo $work_item->title;
    echo "<span class='project_date'>".$work_item->y." / ".$month[$work_item->month]."</span>";
    echo "</a>";
    echo "</li>";
    $max_loop++;
    if($max_loop==4){break;}
    }
}
?>