perl routine for duplicate prevention with mysql database

[jeremy goodrich]

I'm working on a seo tool, for personal use. A clever coworker suggested I log everything, so I thought, "why not mysql?" Anyway, I'm having trouble with the process of 'if it's there, don't write it (the url) if it's not there, write it. following is the code (currently not working)

any ideas? thanks.

[perl]
# data into db, and close connection
my $query = "SELECT urldb FROM keycheck WHERE urldb = \"$urldb\"" or return 0;
my @rows = $dbh->do($query);

if (@rows) {
foreach $row (@rows) { if($row == $urldb) {$dbh->disconnect; } $row++; }
#else { #if its not there, put it there
$statement = "INSERT INTO keycheck VALUES (?, ?, ?, ?)";
$sth = $dbh->prepare($statement) ¦¦ die $dbh->errstr;
$sth->execute("", "", $urldb, $title) ¦¦ die $dbh->errstr; }
}

$dbh->disconnect; #else this just goes away after writing once which is no good.[/perl]

[mark_roach]

Jeremy

A quick answer with no real thought given, so forgive me if I am talking rubbish.

Why not define urldb as a unique key and then just do the insert, I think it will fail if the url is already in your database.

[jeremy goodrich]

he he he, that is a good idea! now, if only I had thought of that before spending (I'm still learning) 4 or so hours on the problem...lol.

here is the new and improved code:
[perl]
# data into db, and close connection
my $query = "SELECT urldb FROM keycheck WHERE urldb = \"$urldb\"" or return 0;
my $rows = $dbh->do($query);

if ($rows == 0) {
#foreach $row (@rows) { if($row eq $urldb) {$dbh->disconnect; } $row++; }
#else { #if its not there, put it there
$statement = "INSERT INTO keycheck VALUES (?, ?, ?, ?)";
$sth = $dbh->prepare($statement) ¦¦ die $dbh->errstr;
$sth->execute("", time(), $urldb, $title) ¦¦ die $dbh->errstr; }
[/perl]

but wait, there's more! I also needed to make sure the urls in the db weren't in there as domain.com/ and domain.com, so I added this:

[perl]$urldb = $q->param('url');
$urldb =~ s/\/$//; #chop the trailing slash if no page name.
[/perl]

[Woz]

quick thought, you will also need to check for domain.com vs www.domain.com.

Onya
Woz

[jeremy goodrich]

thanks for that Woz.

anything else I'm missing?