PHP:MySQL > Check user exist?

[Anon]

I'm using this:

$newuser = "INSERT INTO webappsusers (username,password,email,firstname,surname,companyId,telephone,fax,roleId,permissionId) VALUES ('$frmuser','$frmpass','$email','$firstname','$surname','$company','$phone','$fax','$role','$permissionId')";

$result = mysql_query($newuser);

To enter new users into a MySQL table, but how do I check for uniqueness on email and username?

Thanks

Nick

[Anon]

I would check the uniqueness of the username and email before I executed the query you have listed.

This may not be the slickest way to do it, but I'd run a SELECT query on username and see if I get any results. If not, the username is unique. Do the same for email. If no results, it's unique.

ie..

SELECT username FROM webappusers WHERE username='$frmuser'

...and...

SELECT email FROM webappusers WHERE email='$email'

Then go ahead and execute your INSERT query.

[junebug]

when you create the table in mysql, make the columns unique so they cannot have the same values. to do this you must add a unique index to the table:

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)

if you want to add it to an existing table, use this syntax:

ALTER TABLE tabl_name ADD UNIQUE index_name (column_list)

[[deleted]]

Creating an index is better than running a select query.

Why?
Because there is time between the select and the insert. That means it is possible for someone else to also insert the same data before you can run the insert query.

If you have a unique index, it is simply not possible to insert a duplicate record at all, no matter what you do.

[Anon]

Thanks for the suggestions, I've already set the column to be unique, so how do I go about capturing the error that comes back so I can give the user a 'helpful' error msg?

Thanks

Nick