Adjust mysql database query to filter column value

Question

This should be an easy one for folks who are knowledgeable about database queries (I just started to learn).

In my multiple author site, I use the following function to get the top 2 categories the user has publishing in:

// Function to get the user's top two categories
function GetTop2CategoryByUser($user_id, $taxonomy){
    global $wpdb;

    $results=$wpdb->get_results( $wpdb->prepare(
        "
       SELECT      tt.term_id as category, COUNT(p.ID) as count
       FROM        $wpdb->posts p

       JOIN        $wpdb->term_relationships tr
                   ON p.ID = tr.object_id

       JOIN        $wpdb->term_taxonomy tt
                   ON tt.term_taxonomy_id = tr.term_taxonomy_id
                   AND (tt.taxonomy = %s AND tt.term_taxonomy_id != 1)

       WHERE       p.post_author = %s
       GROUP BY    tt.term_id
       ORDER BY    count DESC LIMIT 2
    ",
        $taxonomy,
        $user_id
    ) );
    return $results;
}

To my question: Under the posts table, I have the column post_status with value published. I want the query to only get results that have post_status set to published.

How do I do this?

Max D · Accepted Answer · 2014-12-09 16:04:22Z

2

just add into the where

and p.post_status = 'published'

answered Dec 9, 2014 at 16:04

Max D

8137 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adjust mysql database query to filter column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related