Stopping duplicate fields

Hi,

I am using a MySQL database and have email address as the Primary Key.
I also store a username of which there should not be duplicates. I am
trying to check for this by doing the following:

$query5 = "SELECT username FROM member WHERE username='$username';";
$result5 = mysql_query($query5) or die('Error during subscription
process, please refresh the page and try again');
$row5 = mysql_fetch_row($result5);
if($row5 == $username1){
$errorlist = errorlist('This username is already taken',
$errorlist);
$errorform = 1;
$usernamestyle = ' ; color:red';
}else{
$querya = "UPDATE member SET username='$username1' WHERE
member_id='$user';";
}

But this doesnt seem to work. Any improvements would be much
appreciated.

Cheers,

Paul

Re: Stopping duplicate fields

"pmorrison" wrote in message
news:1141329833.933769.192920@e56g2000cwe.googlegroups.com.. .
> I am using a MySQL database and have email address as the Primary Key.

This means that no two members can share an email address. Also, when
people change their email address (and they do that frequently), you have to
update the primary key for the member table, and also any fields in other
table that reference it. For these reasons, it is generally preferred to
use something other than the email address as a primary key. Usually an
integer column is used for the primary key, and no other meaning is assigned
to the integer. The email address should be an attribute column in the
table. But this doesn't apply to your question, it's just a tangential
recommendation.

> I also store a username of which there should not be duplicates.

If there should not be duplicates, have you considered creating a UNIQUE
constraint to enforce that? Otherwise, it's as sure as rain that you _will_
end up with duplicates.

ALTER TABLE member ADD UNIQUE (username);

Then you can rely on the constraint to tell you if there's a problem:

$query5 = "UPDATE member SET username='$username1' WHERE member_id='$user'";
$result5 = mysql_query($query5);
if (!$result5) {
if (mysql_errno() == 1062) {
// report error, that the username is already taken
} else {
// report error, something else happened besides a unique key violation
}
}

See http://dev.mysql.com/doc/refman/5.0/en/error-messages-server .html for
errno values.

Regards,
Bill K.