Email Table - Unique

Hi,

I have a form which I will be using to store the names, usernames, password and email addresses for the people.

So there are basically 4 field

name

usernames

email

password

what I want is, that the usernames and email addresses should be unique. I am done with the usrname field by making it primay bt when I make email field as unique, it shows me an error upon second entry. I believe it doing so because of @ and .(dot)

what should I do?

 

You have not specified which Database server you using, but it seems like you want to use a constraint to like constrain the data in the table
Most SQL systems allow you to use constraints to do this altough Mysql is know to ignore this directive. For Mysql you could use an Unique index on multiple columns to get some of the constraint functionality.
Postgresql on the other hand lets you define constraints and checks on the table or columns and enforces this.

Mysql
CREATE UNIQUE INDEX username_email_idx USING BTREE ON yourtable (username,email);

Postgresql
CREATE TABLE ....... UNIQUE (username,email) ...
Look in the manual for the specifics.

 

m Using mysql. Thanx for the information. The one you specified for MySQL, can I use that?

 

Ok, now I got ot working. What I want now is, that to display error message based on the error occurred by sql. Like if username exists or email exists. My form just show the error DUPLICATE entry in key#

This is the form I am using for connecting to Databse.

<?php
$con = mysql_connect("localhost","database","password"); //Replace with your actual MySQL DB Username and Password
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db("database", $con); //Replace with your MySQL DB Name
$name=mysql_real_escape_string($_POST['name']); //This value has to be the same as in the HTML form file
$username=mysql_real_escape_string($_POST['username']); //This value has to be the same as in the HTML form file
$email=mysql_real_escape_string($_POST['email']); //This value has to be the same as in the HTML form file
$password=mysql_real_escape_string($_POST['password']); //This value has to be the same as in the HTML form file
$sql="INSERT INTO table (name,username,email,password) VALUES ('$name','$username','$email','$password')"; /*form_data is the name of the MySQL table where the form data will be saved.

name and email are the respective table fields*/
if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error()); 
} 
echo "The form data was successfully added to your database."; 
mysql_close($con);
?>

PHP:

 

Sorry about that but I am more of a Java or Perl person myself, but I assume you would need to perform some additional logic in your error handler which now only performs an error dump + die.

I would use the mysql_errno() function to get an translation independant indication of the specific error condition . According to the Mysql pages I think it is code 1022,
if (mysql_errno()==1022) { print " fancy HTML formatted error message" ;}

 

and where should I put that code? I mean where in above PHP?

 

I think you should put that in your error handler
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
Currently your error handler is setup to die you should make it a bit smarter and branch depending on the errcode value. If duplicate entry the fancy explanation of not die.
Something like that.