#!/usr/bin/perl

use strict;
use DBD::mysql;
use Data::Dumper;
use Getopt::Long;
my $dbh;

my ($username, $password, $host, $database, $mode) = ("root", "", "localhost", "wordpress", "both");
GetOptions ("dbuser=s" => \$username,   # MySQL username
        "dbpass=s", => \$password,      # MySQL password for above user
        "dbhost=s", => \$host,          # MySQL host
        "dbname=s", => \$database,      # Wordpress database name (finds all tables with comments)
        "mode=s", \$mode);              # links,keywords,both

my $links = {};
my $keywords = {};

my $allblogs = Dbh()->prepare("SHOW TABLES LIKE '%comments'");
$allblogs->execute();
while (my $row = $allblogs->fetchrow_arrayref()) {
        my $comments = Dbh()->prepare(sprintf "SELECT comment_author_url, comment_content FROM %s WHERE comment_approved='0' OR comment_approved='spam'", $row->[0]);

        $comments->execute();
        while (my $comment = $comments->fetchrow_arrayref()) {
                $links->{$comment->[0]}++;
                my @urls = ($comment->[1] =~ /(http[^ ]*)#?/g);
                map {$links->{$_}++} @urls;
                my @keywords = ($comment->[1] =~ /^\s*<strong>(.*?)\.\.\.<\/strong>/g);
                map {$keywords->{$_}++} @keywords;
        }
}

map { print "$_\n"; } keys %$links if ($mode eq "links" or $mode eq "both");
map { print "$_\n"; } keys %$keywords if ($mode eq "keywords" or $mode eq "both");

sub Dbh {
        return $dbh if (defined $dbh);
        my $dsn = "DBI:mysql:database=$database;host=$host";

        $dbh = DBI->connect($dsn, $username, $password) or die $DBI::errstr;
        return $dbh;
}