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 =
DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpa
ssword",{'RaiseError'=>1});
$sth = $dbh->prepare($query);
$sth->execute();
$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);
        $sth->execute();  
        # finish and disconnect
        $sth->finish();
        $dbh->disconnect();

Quote:}

# 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
(prefix,firstname,middlename,lastname,address1,address2,city,state,zip,c
ountry,email,phone,howfound,interest,lastpurch_web,optin,optin_partners,
mail_type,comments,record_type,date,browser) VALUES
(\"$prefix\",\"$firstname\",\"$middlename\",\"$lastname\",\"$address1\",
\"$address2\",\"$city\",\"$state\",\"$zip\",\"$country\",\"$email\")";
        #actually execute the insert
        $sth = $dbh->prepare($sql);
        $sth->execute();  
        # finish and disconnect
        $sth->finish();
        $dbh->disconnect();

Quote:}

providing the finest in midget technology

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php