Sometimes the wordpress category post counts displayed (widgets/admin side) become out of sync, this simple snippet of php code fixed my own website(s) and I hope it helps someone else too. It could be future extended to also clear out orphaned terms too (which caused the out of sync issue to begin with). Usual cause of post count to become out of sync is when plugins clear out redundant posts by just deleting the wp_post table, and not removing term items and adjusting category term count.

This is probably not the neatest bit of code (would be better to use mysql JOIN statements between tables), but unlike other solutions I have seen on the web, this one actually works on my wordpress system.

Basically copy code onto a script under wordpress site (public area), and call it via browser eg http://sitedomainname/fixpostcounts.php

It assumes the prefix of mysql tables starts with the default “wp_” – change accordingly. Will look into making a plugin version aswell (time permitting).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

<?php
// post count per category fix by platinumshore
set_time_limit(0);
 
    require('wp-config.php'); 
 
    $username = DB_USER; 
    $password = DB_PASSWORD; 
    $server = DB_HOST; 
    $dB = DB_NAME; 
    $connect = mysql_connect($server, $username, $password);
    if (!$connect){ 
    die('Could not connect: ' . mysql_error());
    }
    else{
    mysql_select_db($dB, $connect);
    }
 
$query="Select * from `wp_term_taxonomy` where `taxonomy`='category'";
$result = mysql_query($query);
$category_ids = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$category_ids[] = $row["term_taxonomy_id"];
}
mysql_free_result($result); 
 
foreach($category_ids as $category_id) {
$query="Select * from `wp_term_relationships` where `term_taxonomy_id`='".$category_id."'";
$result = mysql_query($query);
$count=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$post_id = $row["object_id"];
// does post id actually exist ?
$num_rows = mysql_num_rows(mysql_query("SELECT * FROM `wp_posts` where `post_status`='publish' and `post_type`='post' and `ID`='".$post_id."'"));
if($num_rows>0) {
$count=$count+1; // count of actual valid posts found
}}
mysql_free_result($result);
 
// get term id for this category id
$query="Select * from `wp_term_taxonomy` where `term_taxonomy_id`='".$category_id."'";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$term_id = $row['term_id'];
 
// get category name from term id
$query="Select * from `wp_terms` where `term_id`='".$term_id."'";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$name = $row['name'];
 
echo("Category Name:".$name.", Real Post Count:".$count."<br>\n");
 
$query = "Update `wp_term_taxonomy` set `count`='".$count."' where `term_taxonomy_id`='".$category_id."'";
mysql_query($query);
 
}
 
 
?>