in reply to Re: Search a SQL Table and Delete Records
in thread Search a SQL Table and Delete Records

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.

Replies are listed 'Best First'.
Re^2: Search a SQL Table and Delete Records
by poj (Abbot) on Aug 30, 2014 at 18:06 UTC
    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
[reply]
[d/l]

      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.

[reply]

      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!

[reply]

In Section Seekers of Perl Wisdom