WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] SQL query to get all posts from a category (5 posts)

  1. klevismiho
    Member
    Posted 2 years ago #

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

    Thank you in advance.

  2. keesiemeijer
    moderator
    Posted 2 years ago #

    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);
  3. klevismiho
    Member
    Posted 2 years ago #

    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.

  4. keesiemeijer
    moderator
    Posted 2 years ago #

    get_posts() makes use of the WP_Query class to fetch posts. WP_Query can filter multiple custom fields with the meta_query argument: http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters

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

  5. klevismiho
    Member
    Posted 2 years ago #

    Yeah you are right, meta_query handles multiple custom fields.

    Very thanks again.

Topic Closed

This topic has been closed to new replies.

About this Topic