Custom Query num_rows returns wrong amount

So I have a custom query that fetches all posts in a given category. I am trying to get the number of rows returned but it is giving me the wrong number.

Instead of 11, I am getting 14. Does anyone know why?

Read More
<?php
$pageposts = $wpdb->get_results($wpdb->prepare("
    SELECT * FROM $wpdb->posts
    LEFT JOIN userContests ON ($wpdb->posts.ID = userContests.contestID)
    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->posts.post_type = 'post' 
    AND $wpdb->posts.post_date < NOW() 
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(3)
    ORDER BY $wpdb->posts.post_date DESC
    "), OBJECT);

/*   $rows = $wpdb->num_rows;  <------ Wrongly returns 14 */

/*   $rows = $wpdb->get_var($wpdb->prepare("      <--------- Correctly returns 11
        SELECT COUNT(*) FROM $wpdb->posts
        LEFT JOIN userContests ON ($wpdb->posts.ID = userContests.contestID)
        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->posts.post_type = 'post' 
        AND $wpdb->posts.post_date < NOW() 
        AND $wpdb->term_taxonomy.taxonomy = 'category'
        AND $wpdb->term_taxonomy.term_id IN(3)
        ORDER BY $wpdb->posts.post_date DESC")); */
echo "Number of rows = " .$rows;
?>

My work around seems like a waste of resources. Is there something more efficient?

Related posts

Leave a Reply

1 comment

  1. If you want the total count of all the published posts in a particular category you can try this.

    $all_posts = new WP_Query( array( 'posts_per_page' => -1, 'post_status' => 'publish', 'cat' => 3 ) );
    echo $all_posts->post_count;
    

    or

    $all_posts = new WP_Query( array( 'posts_per_page' => -1, 'post_status' => 'publish', 'category_name' => 'uncategorized' ) );
    echo $all_posts->post_count;
    

    Just make sure to replace the ‘category_name’ or ‘cat’ with desired category.