Wordpress SQL - How to Check for Category?

Question

I'm using the following SQL (wrapped in php) to find posts in a wordpress database:

$sql = "
    SELECT
        p.*
    FROM
        {$wpdb->prefix}most_popular mp
        INNER JOIN {$wpdb->prefix}posts p ON mp.post_id = p.ID
    WHERE
        p.post_type IN ( $holder ) AND
        p.post_status = 'publish'
    {$order}
    LIMIT %d
";

And I want to add an additional condition that the post be in a specific category. Any thoughts?

Answer 1

And I want to add an additional condition that the post be in a specific category. Any thoughts?

JOINing on the taxonomy tables is complicated. Unless you have a good reason why you have to use SQL, don't. Use WP_Query.

$args = array(
  'post_type'     => $holder,
  'numberposts'   => -1,
  'post_status'   => array('publish'),
  'category_name' => 'cat-slug', // slug not strictly the name
  'orderby'       => $order,
  'ignore_sticky_posts' => true,
);
$qry = new WP_Query( $args );

I am guessing about the content of some of your variables.

If you must use pure SQL, run the query above but add var_dump($qry->request) and you will have the generated SQL dumped to the screen. That will give you an idea of what has to happen.