Adding Unique Records in MySQL

[xXtreme]

Hey guys,

I have a simple form handled by a PHP script to add records to a MySQL database. But how do I check for unique records so that users don't add duplicates?

Thanks.

Chris

[devinemke]

run a select query on the db with your form input data to see if the data already exists. use mysql_num_rows() on your result set. if you get anything other than 0, then display some message saying "you are already in our database...etc..."

[xXtreme]

Good solution, thought of that. But I figured that approach may be a little expensive on the MySQL server for a large database, so I was wondering if a more effecient way existed. Like maybe checking an index.

Thanks though, any other comments are appreciated.

[devinemke]

if you properly index your tables then you will obviously optimize all of your select queries. lets say you have a basic "contacts" table with the following fileds:

contact_id (auto_incrementing primary key)
first_name
last_name
email

if someone who is already in the db (using the same email address) tries to sign up you could do something like:

$result = mysql_query ("SELECT email FROM contacts WHERE email = '" . $_POST['email'] . "'");
if (mysql_num_rows ($result))
{
echo 'your are already in the database';
}
else
{
// database insert code
}

for the above code to be more efficient, your should create an index on the "email" field of the "contacts" table to speed up the select query. your should basically create an index on any field(s) in the "WHERE" clause of your SELECT queries (bear in mind that this may include multi-field indexes).

[xXtreme]

Perfect. Thanks.

Chris

[HalfaBee]

JFYI you can make the column 'UNIQUE' and when you try to do the INSERT with a matching name it returns an error.

HalfaBee

[xXtreme]

Thanks HalfaBee. I tried devinemke's suggestion and it worked great, with the exception of being a little slow despite optimization using indexes.

I'll try the 'UNIQUE' option next.