Deleting Spam From Your Web Site Database
In 2002 I developed a hobby site (www.coolslang.com) to learn PHP, MySQL, Unicode and CSS-P (CSS Positional). I hadn’t looked at the site for years. But when I did visit recently, I was surprised, that not only was the site still running but that I had 7000 comments in the commenting system!
Were all these comments because my site had become insanely popular during my absence? No, unfortunately it was from spam. The usual commenter told me not only how great my site was, but that if I needed some Viagra, this is a link to find it.
How do all these people know that I suffer from ED! I must check again for those hidden cameras in the bedroom. Now, not only did I have a limp dick but also a bloated database. What to do?
Going through 7000 records one-by-one would, of course, be out of the question. But then all of the spam contain hyperlinks to their web sites. So the solution is to delete all the comments with “http://”. First I selected these spam comment records using the following statement to verify that this is indeed what I really wanted:
SELECT * FROM comments WHERE comment-text RLIKE ‘http://’
Then simply substitute “DELETE” for the “SELECT *”
DELETE FROM comments WHERE comment-text RLIKE ‘http://’
Problem solved, well at least one of them…