Fixing category count

Somehow my post counts are incorrect due to inserting rows via php. I have the following code to update the count, is it correct?

global $wpdb;
$result = mysql_query("SELECT term_id,term_taxonomy_id FROM $wpdb->term_taxonomy where taxonomy = 'category'");
while ($row = mysql_fetch_array($result)) {
  $term_taxonomy_id = $row['term_taxonomy_id'];      
  $countresult = mysql_query("SELECT object_id FROM $wpdb->term_relationships WHERE object_id IN (SELECT ID FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish') AND term_taxonomy_id = '$term_taxonomy_id'");
  $count = mysql_num_rows($countresult);
  mysql_query("UPDATE $wpdb->term_taxonomy SET count = '$count' WHERE term_taxonomy_id = '$term_taxonomy_id'");
        }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you just want to update the counts of posts in each term, wp_update_term_count_now( $terms, $taxonomy ) should do it… just pass the terms affected as an array and run it once for each taxonomy you have.

You can also call wp_defer_term_counting( true ) before inserting new rows, and then after adding your posts, catch up on the counts by calling wp_defer_term_counting( false ).

Method 2

There’s a sql script written by someone else that does the job – updates counts for product categories in woocommerce or any other category counts.

Only takes a few seconds to run as well:

https://stackoverflow.com/questions/18669256/how-to-update-wordpress-taxonomiescategories-tags-count-field-after-bulk-impo

Method 3

Example for the answer of goldenapples:

$update_taxonomy = 'my_taxonomy';
$get_terms_args = array(
        'taxonomy' => $update_taxonomy,
        'fields' => 'ids',
        'hide_empty' => false,
        );

$update_terms = get_terms($get_terms_args);
wp_update_term_count_now($update_terms, $update_taxonomy);

Method 4

In my opinion the best way to do this is by using WP-CLI. There’s a command for that:

$ wp term recount category

Find the documentation here: https://developer.wordpress.org/cli/commands/term/recount/

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0