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();
}
# 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();
}
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)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
