properly checking for existing records with perl

properly checking for existing records with perl

Post by Rory O'Conno » Sun, 17 Jun 2001 04:57:26

I am writing a small perl script that will write data to a Mysql
database.  The SQL will need to be different based on whether or not the
e-mail address already exists in the database though.  I coded something
that works, but I would like some feedback as to whether or not this is
the best way.  I need this to be as efficient as possible because there
is occasionally very heavy traffic on it, and there will be over 100,000
records in the database.

Basically, I collect the data, then I query the database to see if the
e-mail exists. and if it returns row(s) then I update the data to the
database.  if it doesn't, i insert the new data into the db.  any help
is appreciated!  here's the code snippet:

# see if the e-mail exists
$query = "SELECT email FROM $table where email = \'$email\'";
$dbh =
$sth = $dbh->prepare($query);
$numrows = $sth->rows;

# if there are rows returned, the e-mail is in there, so we need to
update the record
if ($numrows ge '1') {

        # set the SQL for UPDATE
        $sql = "UPDATE $table SET prefix=\"$prefix\", firstname=\"$firstname\",
middlename=\"$middlename\", lastname=\"$lastname\",
address1=\"$address1\", address2=\"$address2\", city=\"$city\",
state=\"$state\", zip=\"$zip\", country=\"$country\", email=\"$email\"
WHERE email = \"$email\"";
        #actually execute the UPDATE
        $sth = $dbh->prepare($sql);
        # finish and disconnect


# if there are no rows returned, it's a new record, so insert it
else {

        ## Set sql to perfect insert format
        $sql = "insert into $table
mail_type,comments,record_type,date,browser) VALUES
        #actually execute the insert
        $sth = $dbh->prepare($sql);
        # finish and disconnect


providing the finest in midget technology

Before posting, please check:   (the manual)           (the list archive)

Trouble unsubscribing? Try: