SQL SELECT statement that excludes a Category

  • Hi there, I’m working with this SQL query:

    $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC LIMIT " . $limit);

    problem is my SQL skills are not good enough for me to figure out how to modify this query to not return any posts that are included in category ’31’.

    What I have done, is put a another query, within the loop I’ve made, that looks up that categories of each post as it loops through, and skips the loop if one of the categories is ’31’.

    The problem that the out loop’s query has the LIMIT statement, to always deliver X number of posts. But obviously I’m always getting X posts minus how many posts in the query are Category 31.

    Is there a way to do this filtering in a single SQL statement?

    Thanks in advance for any help you can offer.

Viewing 6 replies - 1 through 6 (of 6 total)

  • SELECT ID , post_title
    FROM wp_posts
    WHERE post_status = 'publish'
    AND post_category !=31 <---------
    LIMIT 0 , 30

    — If i understand what you want to do..that query works fine for me..

    You cant just add the extra and that you must be using in the second query you mentioned?

    ## The formatting on that was f’ed up with the backticks but hopefully you got the point.

    SELECT ID, post_title FROM ” . $wpdb->posts . ” WHERE post_status=’publish’ and post_category !=’31’ ORDER BY post_date DESC LIMIT …

    Have a look in your database that post_category the right field

    Too late

    didnt i just say that?

    Thread Starter gbot

    (@gbot)

    Hi there, thanks for your responses.

    That doesn’t seemt to work though – I think because the ‘post_category’ column in the posts table are all zeros. The category ids are stored in the post2cat table, and there can of course be multiple categories assigned to a given post, so they all need to be checked for category ’31’ (in this case).

    I think this requires a JOIN or UNION query or something like that, which I know very little about unfortunately… I will investigate further, thanks again.

    @whooami
    Yeah, it took me five minutes from first view to write it down.

    Added too late….

    Thread Starter gbot

    (@gbot)

    just reporting back, I’ve got a solution that works well for me, this SQL filters a variable passed into the funtion.

    $posts = $wpdb->get_results("SELECT DISTINCT ID, post_title FROM wp_alcoveposts
    LEFT JOIN wp_alcovepost2cat ON (wp_alcoveposts.ID = wp_alcovepost2cat.post_id)
    WHERE post_status='publish' AND (category_id NOT IN (" . $filter . ")) ORDER BY post_date DESC LIMIT " . $limit);

    hope that might help someone else… thanks

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘SQL SELECT statement that excludes a Category’ is closed to new replies.