Welcome to Geeklog Thursday, July 09 2020 @ 02:16 am EDT

Geeklog Forums

using mysql to remove comments


Status: offline

sirromj

Forum User
Newbie
Registered: 19/05/13
Posts: 7
curious
Hi,

I'm still a newb to MYSQL, but have discovered the database is full of comment spam. I'm performing queries to match spammy substrings (of my choosing) and then performing queries on IP addresses and noting the outcome. Some of these latter queries are returning (so far) up to 1500 comments per IP. I'm then using another query to delete all comments matching the IP. This could take some time.

But it's occurred to me that I might be missing something and other tables beside gl_comments might need things adjusting due to my deletions?

I've found one article with 10668 comments on it.

Any recommendations!?

Cheers!

 Quote

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/05
Posts: 1398
Before playing around with MySQL try using the SPAM-X plugin that comes installed with Geeklog.

You can add ips and your own words that will trigger the spam filter when a user tries to post something.

You can also delete spam comments after they have been added. See this link on the demo site for an example:

http://demonext.geeklog.net/admin/plugins/spamx/index.php?command=MassDelete

Tom
One of the Geeklog Core Developers.
 Quote

Status: offline

Dirk

Site Admin
Admin
Registered: 12/01/02
Posts: 13073
Location:Stuttgart, Germany
Quote by: sirromj

But it's occurred to me that I might be missing something and other tables beside gl_comments might need things adjusting due to my deletions?


The comment count in the story also needs to be updated. More importantly, though, is that comments are stored in a tree structure, which needs to be re-balanced. This can not easily be done manually.

So, please try Tom's suggestion (using the Spam-X Mass Delete option) first.

If that doesn't help, writing a short piece of custom code that calls CMT_deleteComment() for each comment to be deleted would be another option. Just don't mess with the gl_comments table directly.

bye, Dirk
 Quote

All times are EDT. The time is now 02:16 am.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content