• Home
  • About 404TS
  • Contact

404 Tech Support

Where IT Help is Found

  • Articles
    • Code
    • Entertainment
    • Going Green
    • Hardware, Gadgets, and Products
    • Management
    • Network
    • News
    • Operating Systems
    • Security and Privacy
    • Software
    • System Administration
    • Talking Points
    • Tech Solutions
    • Web
    • Webmaster
  • Reviews
  • Media
    • Infographics
    • Videos
  • Tech Events
  • Tools
    • How do I find my IP address?
    • Browser and plugin tests
  • Get a Technical Consultation
You are here: Home / Articles / Tech Solutions / How to synchronize your WordPress comment count

How to synchronize your WordPress comment count

2013-05-02 by Jason

A year ago today, I wrote about fighting trackback spam on a self-hosted WordPress site. It involved running a SQL query directly on the database to speed up the clean up process and make it more efficient. Since I was dealing with over 100 spam-linking comments on each post, I could use all the help in that regard I could get. Fortunately, the solution written up there worked out quite well.

Unfortunately, I glossed over a side-effect of the manual clean-up effort. Since I deleted the trackback comments directly from the database, the process did not kick off WordPress’s other functions like updating the comment count on an article. This slight annoyance meant the comment count in an article byline would be extremely inflated over the actual number of comments. I dealt with it for 365 days, through no planning of mine, today I cleaned up the mess with one easy to use query to synchronize the comment count with the actual number of comments received per article. While my problem stemmed from previous modifications taken in the database there are plenty of plugins or other ways for this value to become inaccurate.

I hopped into phpMyAdmin in order to browse the tables, familiarize myself with the database, and see if I could find where the inaccurate number was stored.

Before making changes to your database, I highly recommend making a back up and testing each query.

I actually managed to get lucky and pretty quickly found where the comment count per article was stored. It’s in the ‘posts’ table in a column titled ‘comment_count’. How’s that for obvious? Sure enough, when I looked at the comment_count field for a row, it matched the inaccurate number reported on the article itself. There are a number of columns in the ‘posts’ table, so I did a quick SQL query to just make the table a little more browsable and selected only the fields that were relevant.

[SQL]SELECT ID, post_title, comment_count FROM `wp_posts`[/SQL]

posts comment_count

Even if you have permalinks enabled, you can still quickly jump to an article by visiting the page in your browser as yourdomain.com/?p=[ID] so www.404techsupport.com/?p=1804 brings up the AT&T U-Verse post.

The screenshot above shows the ‘posts’ table after I fixed it. Before, the comment_count column each reported 100+ comments. To get to the problematic posts, you might modify the query to include WHERE ‘comment_count’ > 100.

To find out how many comments were posted on a particular post, I performed a different SQL query.

[SQL]SELECT * FROM wp_comments WHERE comment_post_id= ‘706’[/SQL]

comments comment_post_id

This returned each comment attributed to the article with the ID I entered in the query. The line at the top gave me a count of the total number of comments on that post. While it was informative to review all of the comments and revisit the topics of old articles, I was more interested in getting just the facts so I could keep making progress.

A slight modification to the query used the COUNT() function to return the number of comments attributed to a particular post.

[SQL]SELECT COUNT(*) FROM wp_comments WHERE comment_post_id= ‘706’[/SQL]

Now, to tie the two queries together with an update to the posts.comment_count column, this is the query that you need to run to synchronize the comment count with the actual count.

[SQL]UPDATE wp_posts
SET comment_count=( SELECT COUNT(*)
FROM wp_comments
WHERE wp_posts.id=wp_comments.comment_post_id
)[/SQL]

wp update comment_count

This query worked out quite well for me on WordPress 3.5.1. You will need to replace ‘wp_posts’ and ‘wp_comments’ with the names of your tables in case you have changed your table prefix. The number of rows affected tell you how many posts of yours have any comments.

After running the above query with positive results, you will need to clear your site’s cache before expecting to see the comment count update on an article. Now my site is back in sync and my the comment count on articles will no longer be artificially high and remind me of all the spam I had to clean up.

Filed Under: Tech Solutions, Webmaster

Trending

  • Practicing good hygiene when it comes to IT security
    In Security and Privacy
  • Setting Printing Defaults in Mac OS X through presets and CUPS
    In Tech Solutions
  • Session Manager extension: Prevent Data Loss in Firefox
    In Software, Tech Solutions

Latest Media Posts

Find Out Where To Download SNES ROMs

Find Out Where To Download SNES ROMs

Multifunctional Video Conversion Tools – Wondershare Video Converter

Multifunctional Video Conversion Tools – Wondershare Video Converter

  • Popular
  • Latest
  • Today Week Month All
  • How to ‘Unblock’ multiple files at a time with PowerShell How to 'Unblock' multiple files at a time with PowerShell
  • Command line to take ownership and change permissions Command line to take ownership and change permissions
  • Increase IIS Private Memory Limit to improve WSUS availability Increase IIS Private Memory Limit to improve WSUS availability
  • SOLVED: “This modification is not allowed because the selection is locked.” SOLVED: "This modification is not allowed because the selection is locked."
  • Creating and editing views in phpMyAdmin Creating and editing views in phpMyAdmin
  • customer contactless payment for drink with mobile phon at cafe counter bar,seller coffee shop accept payment by mobile.new normal lifestyle concept The Latest Innovations In Payment Technology
  • How Digital Technology Brought the Rise of the CMO   How Digital Technology Brought the Rise of the CMO  
  • How to Purchase Cryptocurrencies? How to Purchase Cryptocurrencies?
  • Top 6 necessary aspects to consider when hiring Angular developers Top 6 necessary aspects to consider when hiring Angular developers
  • Full guide on drawbacks and benefits of Node.js for making the perfect choice for your business Full guide on drawbacks and benefits of Node.js for making the perfect choice for your business
Ajax spinner

Elevator Pitch

404 Tech Support documents solutions to IT problems, shares worthwhile software and websites, and reviews hardware, consumer electronics, and technology-related books.

Subscribe to 404TS articles by email.

Recent Posts

  • The Latest Innovations In Payment Technology
  • How Digital Technology Brought the Rise of the CMO  
  • How to Purchase Cryptocurrencies?

Search

FTC Disclaimer

404TechSupport is an Amazon.com affiliate; when you click on an Amazon link from 404TS, the site gets a cut of the proceeds from whatever you buy. This site also uses Skimlinks for smart monetization of other affiliate links.
Use of this site requires displaying and viewing ads as they are presented.

Copyright © 2023 · Magazine Pro Theme on Genesis Framework · WordPress · Log in