How to display a specific category using a custom Query?

If you insist on continuing on this path using SQL queries, you should first understand how taxonomy terms connect to posts:

$wpdb->posts.ID <–> $wpdb->term_relationships.object_id
$wpdb->term_relationships.term_taxonomy_id <–> $wpdb->term_taxonomy.term_taxonomy_id
$wpdb->term_taxonomy.term_id <–> $wpdb->terms.term_id

With this in mind, and assuming you’re providing the $taxonomy (string) and $term_slug (string) parameters for the category you’re filtering by, I envision your code being written as:

function get_top_posts_by_term($taxonomy = 'category', $term_slug = '')
{
    if(empty($term_slug)) {
        // Can't continue without valid inputs
        return FALSE;
    }
    global $wpdb;

    $query = $wpdb->prepare("
        SELECT 
                p.ID,
                p.post_title AS title,
                p.post_excerpt AS excerpt
            FROM $wpdb->posts p
                INNER JOIN $wpdb->term_relationships rel
                    ON p.ID = rel.object_id
                INNER JOIN $wpdb->term_taxonomy tax
                    ON rel.term_taxonomy_id = tax.term_taxonomy_id
                INNER JOIN $wpdb->terms t
                    ON tax.term_id = t.term_id
            WHERE p.post_type="post"
                AND p.post_status="publish"
                AND tax.taxonomy = %s
                AND t.slug = %s
            ORDER BY p.post_date DESC LIMIT 4;",
        $taxonomy, // the first %s (tax.taxonomy)
        $term_slug // the second %s (t.slug)
    );
    $posts = $wpdb->get_results($query);

    return $posts;
}

I favor using double-quoted PHP strings when dealing with SQL queries because

I can use string interpolation with the $wpdb->{table name} variables.
SQL strings, where used, tend to favor single-quoted strings (I think MySQL is a bit lax in this regard, but it’s a good practice to be in). Double-quoted PHP strings let me use single-quoted string literals without leaving my string or inserting escape characters.

Related Posts: