Are you noticing an incorrect comment count after importing your WordPress site?
Importing a WordPress site using the built-in importer can sometimes mess up your WordPress comments count.
In this article, we will show you how to fix category and comments count after importing WordPress.
What Causes Category and Content Count Error in WordPress?
When you import your WordPress site using the built-in importer, it can sometimes show an inaccurate comments count or none at all.
While all the WordPress comments are safely imported and visible in the admin area, your posts will show an incorrect comment count on your website. This same import error can also impact category count and custom taxonomy count.
As you noticed in the screenshot above, after the import our comment count and category count are showing 0 instead of the actual number.
That said, let’s take a look at how to fix this issue and see accurate comments count in WordPress.
Fixing Category and Comment Count in WordPress
First, you need to create a complete WordPress backup of your site. You should do this every time before performing a major change on your site.
We recommend using Duplicator because it is the most comprehensive WordPress backup plugin on the market.
For more details, please see this guide on how to backup your WordPress site with Duplicator.
Once you have made the backup, let’s move on to the next step.
Next, open a plain text editor like Notepad and simply copy and paste the following code:
<?php
include("wp-config.php");
$myConnection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)) { die('Could not connect: ' . mysqli_error()); }
if (!mysqli_select_db($myConnection, DB_NAME)) { die('Could not connect: ' . mysqli_error()); }
$result = mysqli_query($myConnection, "SELECT term_taxonomy_id FROM ".$table_prefix."term_taxonomy");
while ($row = mysqli_fetch_array($result)) {
$term_taxonomy_id = $row['term_taxonomy_id'];
echo "term_taxonomy_id: ".$term_taxonomy_id." count = ";
$countresult = mysqli_query($myConnection, "SELECT count(*) FROM ".$table_prefix."term_relationships WHERE term_taxonomy_id = '$term_taxonomy_id'");
$countarray = mysqli_fetch_array($countresult);
$count = $countarray[0];
echo $count."<br />";
mysqli_query($myConnection, "UPDATE ".$table_prefix."term_taxonomy SET count = '$count' WHERE term_taxonomy_id = '$term_taxonomy_id'");
}
$result = mysqli_query($myConnection, "SELECT ID FROM ".$table_prefix."posts");
while ($row = mysqli_fetch_array($result)) {
$post_id = $row['ID'];
echo "post_id: ".$post_id." count = ";
$countresult = mysqli_query($myConnection, "SELECT count(*) FROM ".$table_prefix."comments WHERE comment_post_ID = '$post_id' AND comment_approved = 1");
$countarray = mysqli_fetch_array($countresult);
$count = $countarray[0];
echo $count."<br />";
mysqli_query($myConnection, "UPDATE ".$table_prefix."posts SET comment_count = '$count' WHERE ID = '$post_id'");
}
?>
You need to replace DB_HOST, DB_USER, DB_PASSWORD with your WordPress database host (usually the localhost), database username, and password.
You can find all this information by logging into your WordPress hosting cPanel or by looking at your wp-config.php file using a file manager.
Once you have replaced the information, save this file as comments-fix.php
on your desktop.
Now you will need to upload this file to your site’s root directory. You can do that by using an FTP client or by using the file manager in your web hosting control panel.
You follow our step-by-step guide on how to use FTP to upload files to WordPress for more information.
After uploading the file to your website, you need to open your web browser and go to this file:
https://example.com/comments-fix.php
Replace example.com
with your site’s address.
Visiting this file in your browser will run the script which simply loops through your posts, category, tags, comments, and updates the count.
Important: Once you’re done fixing your WordPress comment count, you need to delete comments-fix.php
file from your server.
We hope this article helped you learn how to fix category and comments counts after WordPress import. You may also want to check out our guide on how to solve the most common WordPress errors and our ultimate WordPress SEO guide to improve your rankings.
If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.
Fitri says
Mine is not working, all the count from comments-fix.php is appear, but when i go to single page product it still says Reviews(0)
WPBeginner Support says
Your server may be running on a newer PHP version, this code was for php 5.5 and we will certainly take a look into what we can do to update the code.
Admin
Carlos says
Thanks for this tutorial! Once I switched to PHP 5.5 the script worked like a charm.
WPBeginner Support says
Glad it worked for you
Admin
Peter Edwards says
I lost most of the tag counts in a recent import – fixed this by bulk editing all the posts which were tagged and clicking update (without any changes). This triggers the tag count to be updated by wordpress core. Not sure if a similar trick would work for comments?
Bonnie says
Ooo, such a simple solution, and it worked! Thanks!
Kingsley Felix says
anyone for duplicate comments?
Pare says
Just using cPanel go to PhpMyAdmin
Check What is category id (term_id) in table “xxx_terms” and then update count value in column count into the table “xxx_term_taxonomy” where the term_id is equal term_id of xxx_terms table which that category exists on.
Forexample “uncategory” of product is term_id = 10 and count value is wrong, I update count value to be correct number in table “xxx_term_taxonomy” where term_id = 10. by manual on cPanel – PhpMyAdmin tool.
Phuc says
I get an error 500 if I want to access the comments-fix.php file. How can I fix this?
WPBeginner Support says
Hello,
Please see our guide on how to fix 500 internal server error.
Admin
Jay says
To echo Yuriy’s comments, it’s not working for me either with PHP 7. 500 error.
Yuriy Smirnov says
Doesn’t work on php7, because mysql_connect function has been removed since 7.0.0 verstion.
Rianta D. Mulyana says
Thanks brother, it works!
Cosmin says
Quick question: when replacing the DB_HOST, DB_USER, DB_PASSWORD and DB_NAME do we need to use “” or ” to enclose those values in? Or do we simply paste the values?
I tried and I am getting 500 Internal Server Error while accessing the uploaded file
Tyler says
Yes, you do need to include single quotes around those values. Author should update the snippet to reflect that. Also, author forgot to mention that DB_NAME must also be changed.
Easy to spot, but this tutorial will fail for anyone following the instructions explicitly.
Makinde says
Thanks WPBeginner, This was helpful..
Thanks for always being there for us.
Appreciate!
Aeryn Lynne says
After a disastrous comment import that involved Intense Debate (only thing we could do is import bits of the xml file directly into mysql after obtaining post IDs for nearly a thousand posts,) I’m definitely in need of a quick program like this to correct the count, so thank you!
If anyone is looking to fix the comment count for one or two posts only though: instead of accessing FTP and mysql, they just need click Edit on one of the comments of the post that needs correction, and then click Update, and the post then updates the comment count for all comments involved in that post.
Naki Biga says
Work like a charm, thank you very much!
Very simple and usefull code!
Edwin Rio says
hi …
i have migrated one sito into another (i was using disqus for comments)
now in my new site, i can see the comments on the comments area but once i activate the disqus plugin the comments are not appearing on the front end article,
is this solution for my problem as well or my problems its most on disqus side ?
Thanks !
union says
hi …
i have migrated one sito into another (i was using disqus for comments)
now in my new site, i can see the comments on the comments area but once i activate the disqus plugin the comments are not appearing on the front end article,
is this solution for my problem as well or my problems its most on disqus side ?
Thanks !
Connor Rickett says
That seems fairly straightforward. Thanks for putting that code together, that’s a huge help!