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?
<?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?
If you want the total count of all the published posts in a particular category you can try this.
or
Just make sure to replace the ‘category_name’ or ‘cat’ with desired category.