How to display posts of specific category using a custom Query in WordPress?

Question

Hi I want to display a specific category from a custom query in WordPress. My code works fine and it gets the latest 4 posts, but what I want now is to retrieve a specific category. My code is below thanks :

global $wpdb;

$posts = $wpdb->get_results('SELECT ID, post_title AS title, post_excerpt AS excerpt FROM '.$wpdb->posts.' WHERE post_type = "post" AND post_status = "publish" ORDER BY post_date DESC LIMIT 4');

And here is my full code :

global $wpdb;


$posts = $wpdb->get_results('SELECT ID, post_title AS title, post_excerpt AS excerpt FROM '.$wpdb->posts.' 
 WHERE  post_type = "post" AND post_status = "publish" ORDER BY post_date  cat = "category_id" DESC LIMIT 4');

$items = array();
foreach ($posts as $post) {


    $item = array();
    $item['title'] = get_the_title($post->ID);
    $item['url'] = get_permalink($post->ID);
    $item['desc'] = $post->excerpt;
    $item['image'] = wp_get_attachment_url( get_post_thumbnail_id($post->ID));
    $item['thumb'] = get_post_meta($post->ID, 'Thumbnail', true);
    $items[] = $item;
}

return $items;

Now i want to add the final out pout see the code below :

" id="nav-fragment-"> "> " class="ui-tabs-panel" style=""> " alt="" /> " >

" >Read more

Answer 1

the recommended way is:

    <?php
    $query = new WP_Query('category_name=Category&posts_per_page=4');
    if($query->have_posts()) : while($query->have_posts()) : $query->the_post();
    if (has_post_thumbnail()) {
        ?>
            <a href="<?php the_permalink(' ') ?>" title="<?php the_title(); ?>"><?php the_post_thumbnail(); ?></a>
        <?php
    }
    ?>
    <h2><a href="<?php the_permalink(' ') ?>" title="<?php the_title(); ?>"><?php the_title(); ?></a></h2>
    <?php
    the_excerpt(); // or the_content(); for full post content
    endwhile;endif;
?>

Answer 2

All good, the very first suggestion from @Ammu was also working. I needed to change the values from the array to match the statement here is the code:

global $wpdb;
$cat_id = 10; 

$posts = $wpdb->get_results("SELECT 
ID, post_title AS title, post_excerpt AS excerpt FROM $wpdb->posts p
JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
JOIN $wpdb->terms t ON (tt.term_id = t.term_id)
WHERE p.post_type='post'
AND p.post_status = 'publish'
AND tt.taxonomy = 'category'
AND t.term_id = $cat_id
ORDER BY post_date DESC LIMIT 4");