Hi,
I've written the following Perl script to interact with an mySQL database when a form is submitted. How do I make the script check to see if the input 'username' already exists and returns a message saying that the username exists if it does and that they should click back and choose another?
My Coding: (any errors in it???)
-----------------------------------------------
#!/usr/bin/perl
# Use the DBI module
use DBI qw
sql_types);
# Declare local variables
my ($databaseName, $databaseUser, $databasePw, $dbh);
my ($stmt, sth, @newRow);
my ($username, $password, $firstname, $lastname, $street, $town, $county, $postcode, $country, $email, $phone, $fax, $domain1, $domain2, $domain3, $domain4, $domain5, $domain6, $domain7, $domain8, $domain9, $domain10, $domain11, $domain12, $domain13, $domain14, $domain15, $domain16, $domain17, $domain18, $domain19, $domain20);
# Set the parameter values for the connection
$databaseName = "DBI:mysql:**********";
$databaseUser = "*********";
$databasePw = "********";
# Connect to the database
# Note this connection can be used to
# execute more than one statement
# on any number of tables in the database
$dbh = DBI->connect($databaseName, $databaseUser,
$databasePw) || die "Connect failed: $DBI::errstr\n";
# Create the statement.
$stmt = "INSERT INTO phpSP_users (user, password, userlevel, firstname, lastname, street, town, county, postcode, country, email, phone, fax, domain1, domain2, domain3, domain4, domain5, domain6, domain7, domain8, domain9, domain10, domain11, domain12, domain13, domain14, domain15, domain16, domain17, domain18, domain19, domain20)
VALUES (?username? ?password? ?? '$firstname', '$lastname', '$street', '$town', '$county', '$postcode', '$country', '$email', '$phone', '$fax', '$domain1', '$domain2', '$domain3', '$domain4', '$domain5', '$domain6', '$domain7', '$domain8', '$domain9', '$domain10', '$domain11', '$domain12', '$domain13', '$domain14', '$domain15', '$domain16', '$domain17', '$domain18', '$domain19', '$domain20')";
# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";
# INSERT does not return records
# Clean up the record set
$sth->finish();
$dbh->disconnect();
print <<"EOT";
<HTML>
<HEAD>
<TITLE>Completed User Reg</TITLE>
<meta http-equiv="refresh" content="1; url=*************">
</HEAD>
<BODY BGCOLOR=FFFFFF TEXT=000000 font face=Arial>
<H2>Registration Complete!</H2>
<P>
You are now being redirected to the login page!
EOT
exit;
---------------------------------------------
Thanks for any help!
Robert KerryMake the column "username" Primary or unique key, then trap the error message using \%attr.
Another way is to check if username already exists before inserting:
my $sth = $dbh->prepare("select * where username = '$username'");
my $res = $sth->execute();
$sth ->finish;
if ($res > 0){
#username exists
....
}
else {
# does not exists
# do insert
....
}
I've written the following Perl script to interact with an mySQL database when a form is submitted. How do I make the script check to see if the input 'username' already exists and returns a message saying that the username exists if it does and that they should click back and choose another?
My Coding: (any errors in it???)
-----------------------------------------------
#!/usr/bin/perl
# Use the DBI module
use DBI qw
sql_types);# Declare local variables
my ($databaseName, $databaseUser, $databasePw, $dbh);
my ($stmt, sth, @newRow);
my ($username, $password, $firstname, $lastname, $street, $town, $county, $postcode, $country, $email, $phone, $fax, $domain1, $domain2, $domain3, $domain4, $domain5, $domain6, $domain7, $domain8, $domain9, $domain10, $domain11, $domain12, $domain13, $domain14, $domain15, $domain16, $domain17, $domain18, $domain19, $domain20);
# Set the parameter values for the connection
$databaseName = "DBI:mysql:**********";
$databaseUser = "*********";
$databasePw = "********";
# Connect to the database
# Note this connection can be used to
# execute more than one statement
# on any number of tables in the database
$dbh = DBI->connect($databaseName, $databaseUser,
$databasePw) || die "Connect failed: $DBI::errstr\n";
# Create the statement.
$stmt = "INSERT INTO phpSP_users (user, password, userlevel, firstname, lastname, street, town, county, postcode, country, email, phone, fax, domain1, domain2, domain3, domain4, domain5, domain6, domain7, domain8, domain9, domain10, domain11, domain12, domain13, domain14, domain15, domain16, domain17, domain18, domain19, domain20)
VALUES (?username? ?password? ?? '$firstname', '$lastname', '$street', '$town', '$county', '$postcode', '$country', '$email', '$phone', '$fax', '$domain1', '$domain2', '$domain3', '$domain4', '$domain5', '$domain6', '$domain7', '$domain8', '$domain9', '$domain10', '$domain11', '$domain12', '$domain13', '$domain14', '$domain15', '$domain16', '$domain17', '$domain18', '$domain19', '$domain20')";
# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";
# INSERT does not return records
# Clean up the record set
$sth->finish();
$dbh->disconnect();
print <<"EOT";
<HTML>
<HEAD>
<TITLE>Completed User Reg</TITLE>
<meta http-equiv="refresh" content="1; url=*************">
</HEAD>
<BODY BGCOLOR=FFFFFF TEXT=000000 font face=Arial>
<H2>Registration Complete!</H2>
<P>
You are now being redirected to the login page!
EOT
exit;
---------------------------------------------
Thanks for any help!
Robert KerryMake the column "username" Primary or unique key, then trap the error message using \%attr.
Another way is to check if username already exists before inserting:
my $sth = $dbh->prepare("select * where username = '$username'");
my $res = $sth->execute();
$sth ->finish;
if ($res > 0){
#username exists
....
}
else {
# does not exists
# do insert
....
}