Right now I just want to test something simple, by just searching one field in the record for keywords like http, www, .com, etc. I know I could do this in the database, but since I'm learning a bit of perl scripting, and I can run it on my site, I thought this would be a good way to learn how I can apply things together. Thank you for replying =)

Learning Perl? Excellent!

Learning Perl by applying it when not needed and in a way that makes the specific problem more complex to solve? Not usually advised.

But for this quest: use DBI as recommended above; capture each row's id and the relevant field's contents to an array; use a regular expression to search the elements for whatever words you consider earmarks of spam (and NB roboticus' comment thereon) and when found, use the row id to tell your db engine to delete those rows.

Adopting this scheme will be a SMOP... but one that is actually fairly 'simple' and one which provides a really relevant problem case for your effort to learn Perl.




check Ln42!

Oh cool! I was wondering how I could get it to figure out which records it needed to delete. I will see if I can get this sorted out tonight. I understand the DBI part, it's just getting the searching and deleting part pieced together. Thank you both for the tips!

Will this work for retrieving the two columns I want?

# Search the needed table and pull out needed columns
my $search = "SELECT comment_ID,comment_content FROM wp_comments"
my $sql = $db->prepare($search);
$sql->execute();

# Input fetched data into an array
my @retrievedData = "";
while (my @row = $sql->fetchrow_array()) {
    push @retrievedData, [@row];
}
[download]

If that does I'm still trying to figure out how to search through the results. I only need to search through comment_content, and if a keyword is found, use the comment_ID to delete that row.

See Regular Expressions perlre

#!perl
use DBI;
use strict;
my $dbh = get_dbh(); # connect to db

# build regex
my @keyword = qw(www http .com);
my $words = join '|',@keyword;
my $regexp = qr/$words/i;
print "Regular Expression is $regexp\n";

# search for key words
my $sql = 'SELECT comment_ID,comment_content 
           FROM wp_comments';
           
my $sth = $dbh->prepare($sql);
$sth->execute();

my @spam = ();
while (my ($id,$content) = $sth->fetchrow_array()) {
  if ($content =~ /$regexp/ ){
    print "ID :  $id\ncomment : $content\n\n";
    push @spam,$id;
  }  
}

# delete records
my $sql_del = 'DELETE FROM wp_comments
               WHERE comment_ID = ?';

my $sth_del = $dbh->prepare($sql_del);
for my $id (@spam){
  # enable this when you are sure it's working !!
  #$sth->execute($id); 
  print "Deleted $id\n";
}
[download]

poj

I doubt that database would wait for you to DELETE after SELECT holding other changes at bay without a transaction, which would result in very possibly different messages in the two searches.

I am sorry it has taken me so long to get back here to reply, but I was working my way up to finals for this college quarter and had to put this on the back burner for a while.

poj, your code was really helpful, but in the end, to get it to work, I moved the $sql_del and sth_del down into the for loop. Using strict also caused problems for me, so I just left it out for now. I was able to test this via a table I set up on WampServer, so I should be okay to execute this on my actual server now

Thank you all for all of your help and tips!