0

This is what I have:

global $wpdb, $month;

$months = $wpdb->get_results( "SELECT *
    FROM $wpdb->posts
    WHERE post_type = 'post' AND post_status = 'publish'
    AND DATE_FORMAT( post_date_gmt, '%Y' ) = $y
    GROUP BY DATE_FORMAT( post_date_gmt, '%Y-%m' )
    ORDER BY post_date_gmt DESC
");

The problem is, this returns all posts from that time, but I want only posts that are in category 11.

Is there an easy way to achieve this?

  • 1
    You don't need to use wpdb. You can use WP_Query to get all posts from a certain category. – Gyanendra Giri Nov 28 '15 at 0:32
2

it's quite easy:

$months = $wpdb->get_col("SELECT DATE_FORMAT(post_date_gmt, '%m')
                            FROM $wpdb->posts
                            LEFT JOIN  $wpdb->term_relationships  as t
                            ON ID = t.object_id
                            WHERE post_type = 'post' AND post_status = 'publish' AND t.term_taxonomy_id = 11
                            AND DATE_FORMAT(post_date_gmt, '%Y') = $y
                            GROUP BY DATE_FORMAT(post_date_gmt, '%Y-%m')
                            ORDER BY post_date_gmt DESC");

term_taxonomy_id is the category. And I join on the object ID

1

The best way to get all posts from a specific category within WordPress is to use the native class WP_Query, and not the $wpdb method.

Here is a query example using WP_Query:

<?php

    $args = array(
        // Arguments for your query.
    );

    // Custom query.
    $query = new WP_Query( $args );

    // Check that we have query results.
    if ( $query->have_posts() ) {

        // Start looping over the query results.
        while ( $query->have_posts() ) {

            $query->the_post();

            // Contents of the queried post results go here.

        }

    }

    // Restore original post data.
    wp_reset_postdata();

?>

In your scenario all we need to do now is specify in the query arguments the category parameter cat and your desired category's ID, like so:

$args = array(
    'cat' => '11',
    'post_type' => 'post',
    'post_status' => 'publish'
);

There are many more parameters and values you can feed to the query's argument.

Hope this helps!

  • can you implement the rest of the query as well using $args ? because the reason I was using the raw db query was because it would not let me solve exactly what I wanted, using WP_Query – Toskan Aug 9 '17 at 19:51
  • @Toskan What 'exactly' do you want to do, then? You've only mentioned needing to get all posts from a specified category. Maybe you could clarify your additional requirements by editing your original post? – Aaron Lynch Aug 9 '17 at 22:23
  • basically everything that is in the wpdb sql query needs to work, I guess otherwise I wouldn't have done it (this is a question from 2015). Post type and status you can get via wp_query I guess. DATE_FORMAT(post_date_gmt, '%Y') is the year like 2017 so all posts in that year, grouped by year + month and ordered by date. I will upvote you because 600 views is a lot and I guess most people want your answer, not my custom query – Toskan Aug 10 '17 at 17:12
0

To show all posts from a category ID 11. Use following loop. It will show all posts from category ID 11.

// WP_Query arguments for category Id 11
 $args = array (
     'cat'                    => '11',
 );

// The Query $query = new WP_Query( $args ); // The Loop if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); // rest of loop code } } else { // no posts found }

// Restore post data loop wp_reset_postdata();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.