Is it possible to insert a row but only if a value does not already exist?

Question

Is it possible to insert a row, but only if one of the values already in the table does not exist?

I'm creating a Tell A Friend with referral points for an ecommerce system, where I need to insert the friend's email into the database table, but only if it doesn't already exist in the table. This is because I don't want any more than 1 person getting the referral points once the new customer signs up and purchases something. Therefore I want only one email ever once in the table.

I'm using PHP 4 and MySql 4.1.

Answer 1

This works if you have a unique index or primary key on the column (EmailAddr in this example):

INSERT IGNORE INTO Table (EmailAddr) VALUES ('[email protected]')

Using this if a record with that email already exists (duplicate key violation) instead of an error, the statement just fails and nothing is inserted.

See the MySql docs for more information.