Mass delete bbpress spam posts

Having neglected a wordpress/bbpress installation for a bit I had the problem of dumping tens of thousands of spam and trash and pending items from bbpress.

I took a look at the database:

HeidiSQL WordPress wp_posts

To get things done more quickly than using the web browser I deleted the items as follows.

I created a php script to retrieve the IDs of the items I needed to remove:

$strSql="SELECT ID FROM wp_posts WHERE post_status='spam'";

$con=mysqli_connect("localhost","my_wordpress_user","my_db_pass","my_wordpress_db_name") or die("Error connecting");
$result = mysqli_query($con, $strSql);
while($row = mysqli_fetch_array($result)){
 printf($row['ID']."\n");
 }
mysqli_close($con);

I ran this code as follows:

php ./getPostIds.php > getPostIdsSpam.out

Then used that output as input for the following:

$con=mysqli_connect("localhost","my_wordpress_user","my_db_pass","my_wordpress_db_name") or die("Error connecting");
$file = fopen("getPostIdsSpam.out", "r") or exit("Unable to open file!");
while(!feof($file)){
  $intId = fgets($file);
  $result = mysqli_query($con, "DELETE FROM wp_posts WHERE ID=".$intId);
  $result2 = mysqli_query($con, "DELETE FROM wp_postmeta WHERE post_id=".$intId);
  if($result && $result2){
        printf("Deleted id ".$intId);
        }else{
        printf("Results not positive".$result." ".$result2." for ".$intId." ");
        }
  }
fclose($file);
mysqli_close($con);

I repeated the process several times changing post_status in the SQL to trash, pending, auto-draft etc. And afterwards optimised the database tables i.e. OPTIMIZE TABLE wp_posts;

Not perfect or pretty but got the job done while I implemented anti-spam measures.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>