PHP script to remove links from WordPress comments

Mark Zeni

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.

*Please only use the script if you know what you are doing, And certainly do so at your own risk. As you can see I recommend below backing up your whole database in case anything goes wrong. The worst case scenario is that it will erase all the comments in your database, so you need to be able to revert it.

<?php
$db = mysqli_connect(‘localhost’,’db_user’,’password’,’db_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 $id. “,”;

$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.Export both the whole database and the comments table separately so that you can restore it easily.

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).

Start Your Free Trial Today!


Browse all articles on the WordPress category or check the recommended articles for you below:

Comments are closed.