Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
    
"I have a custom query that fetches all posts in a given category" - Why? The WordPress query class supports querying on categories already, so why the custom SQL statement? –  t31os Nov 23 '11 at 20:30
    
because I'm joining it with my own tables to add some values. –  Sweepster Nov 23 '11 at 20:51
1  
You could still do a join using a filter on posts_join. –  t31os Nov 24 '11 at 12:32
    
lol if you're willing to translate my query into one that uses filters you are more than welcome. I for one don't understand how to use 'em. I'm not building a plugin, I'm hardcoding this into my page. –  Sweepster Nov 24 '11 at 15:03
    
Does your first query actually return 14 results? Like, if you loop through the object (not relying on the num_rows for a result count)? I guess my question is, is it just that num_results is not being set correctly, or do those two queries actually return different numbers of results? –  MathSmath May 21 '12 at 23:13

1 Answer 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.

share|improve this answer
    
That's what my workaround does, just with a custom query. The thing is though that num_row is SUPPOSED to return the number of rows returned by the last query made but clearly this isn't happening. I'm just wondering if there was a more efficient way than making two queries. –  Sweepster Nov 23 '11 at 4:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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