Today I was doing some cleanup on the blog and I discovered that many of the older comments had what could be considered spam links inside the body of the comment itself.
Instead of manually going through each one, which would take a lot of time, I decided to create a small PHP script to do this task automatically for me. Below you will find the script and an explanation of what it does.
<?php
$db = mysqli_connect('localhost','database_user','password','database_name')
or die('Error connecting to MySQL server.');
$query = "SELECT comment_ID,comment_content FROM wp_comments";
$result = mysqli_query($db,$query)
or die('Error querying database.');
while($row = mysqli_fetch_array($result)){
$id = $row[0];
$content =strip_tags($row[1]);
$regex = "@(https?://([-w.]+[-w])+(:d+)?(/([w/_.#-]*(?S+)?[^.s])?).*$)@";
$content = preg_replace($regex, ' ', $content);
//echo $content. “<br>”;
$stmt = mysqli_prepare($db,”UPDATE wp_comments SET comment_content=? WHERE comment_ID = ?”);
mysqli_stmt_bind_param($stmt,”si”,$content, $id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
?>
How to use the script above:
0. Important: backup your database before doing anything.
1. Copy and paste the script into a text file and save it with the PHP extension, for example you can call with comments.php
2. Update the database connection with your user and database credentials
3. Upload the file to your website
4. Run the file by accessing it using your browser
That’s it. The script will run through all your comments removing all links (but preserving the text of the links).