Hi,
Before doing the insert statement run a SELECT query from the table
specifying the e-mail address in the WHERE condition.
i.e. use something like:
my $sql = "SELECT 1
FROM USERS
WHERE e-mail = $email";
my $sth = $dbh->prepare($sql);
$sth->execute();
my $val = $sth->fetchrow_array();
if ($val) {
# a row was returned therefore e-mail in DB
# output error-handling page
}
else {
# no row returned so do insert statement
# and write normal page
}
Hope this helps,
Paul
On Mon, 20 Jan 2003, Hughes, Andrew wrote:
> I am relatively new to this list and perl and DBI as well. The goal is that
> before users submit a form submission to the database, I want to make sure
> that someone with the same email address has not already submitted it. The
> Email field is marked as UNIQUE() in the MySQL database table, so when
> someone trys to enter twice, there is an error and the record does not get
> entered. However, I would like to display a custom page that nicely
> explains that we apprecitate their interest, but they have already been
> entered into the database. With this code, I recieve the Carp error in the
> browser. When I turn off Carp, the script acts as if the entry was added
> (no error displayed), but the record is not entered. Can anyone offer
> suggestions on how to make decisions based on a DBI error message ?
>
> The code that adds people to a MySQL database table using the following
> insert statement:
>
> #! /usr/bin/perl -T -w
> # survey.pl
>
> use strict;
> use lib qw(/home/ahughes/myLibrary);
> use CGI qw(:standard escape escapeHTML);
> use CGI::Carp qw(fatalsToBrowser);
> use DBI;
> use WebDB;
>
> ....
>
> sub insert_data {
>
> my $dbh = WebDB::connectSurvey ();
> my $sth;
>
> $dbh->do (qq{INSERT INTO nationalConsumerShoppingSurvey SET
> participantId = ?,
> participantDate = NOW(),
> participantFirstName = ?,
> participantLastName = ?,
> participantPhone = ?,
> participantPhoneExtension = ?,
> participantDivision = ?,
> participantEmail = ?
> },
>
> undef,
> undef,
> $participantFirstName,
> $participantLastName,
> $participantPhone,
> $participantPhoneExtension,
> $participantDivision,
> $participantEmail
> ) or surveyErrTrap("Cannot enter record because of duplicate email");
> return my $dbh;
>
> $dbh->disconnect ();
>
> }
>
> sub surveyErrTrap
> {
> my $error_message = shift(@_);
> die "$error_message\n ERROR: $DBI::err ($DBI::errstr)\n";
> }#end: err_trap
>
> .....
>
> ######################################
>
>
> Thanks,
> Andrew
>
