Support » Fixing WordPress » SQL query to get all posts from a category

  • Resolved klevismiho


    Can anyone help me on how to get all posts from a category?
    It must be a SQL query.

    Thank you in advance.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator keesiemeijer


    Why not use get_posts?

    global $wpdb;
    $query = "
    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON
    ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON
    ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id = 1
    ORDER BY post_date DESC
    $results = $wpdb->get_results($query);

    First of all thank you, your query works, I really appreciate it.

    My query was:

    SELECT DISTINCT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->terms wterms
    WHERE wposts.ID = wpostmeta.post_id
    AND wposts.post_status = 'publish'
    AND wposts.post_type = 'post'
    AND wterms.term_id = 3

    In your query I noticed that you have not set the post_type = ‘post’. Why is that? Is it not needed?

    I want to create a search by filter, multiple custom fields are involved so I’ve read that get_posts() doesn’t handle multiple custom fields.

    Thank you again.

    Moderator keesiemeijer


    get_posts() makes use of the WP_Query class to fetch posts. WP_Query can filter multiple custom fields with the meta_query argument:

    If you only want the “post” post type you can put this in the query:
    AND $wpdb->posts.post_type = 'post'

    Yeah you are right, meta_query handles multiple custom fields.

    Very thanks again.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘SQL query to get all posts from a category’ is closed to new replies.