mysql - 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 know I could do this with 2 queries, but I imagine one of you sharp persons out there could do it in one? I tried but kept getting MySql errors.

Thanks in advance!

Oh and I'm using PHP 4 and MySql 4.1

Answer 1

Hi there!

I'm not sure if I got it, but what about a

try {
  mysql_query($sql);
}
catch(Exception $e) {

}

combined with an unique field index in MySQL?

if it throws an exception then you know that you got a duplicated field. Sorry if that don't answer your question..

Bye!

Answer 2

If the email field was the primary key then the constraints on the table would stop a duplicate from being entered.

Answer 3

If the column is a primary key or a unique index:

INSERT INTO table (email) VALUES (email_address) ON DUPLICATE KEY UPDATE
email=email_address

Knowing my luck there's a better way of doing it though. AFAIK there's no equivalent of "ON DUPLICATE KEY DO NOTHING" in MySQL. I'm not sure about the email=email_Address bit, you could play about and see if it works without you having to specify an action. As someone states above though, if it has unique constraints on it nothing will happen anyway. And if you want all email addresses in a table to be unique there's no reason to specify it as unique in your column definition.

Answer 4

Most likely something like:

IF NOT EXISTS(SELECT * FROM myTable WHERE Email=@Email) THEN INSERT INTO blah blah

That can be rolled into one database query.

Answer 5

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.

Answer 6

A slight modification/addition to naeblis's answer:

INSERT INTO table (email) VALUES (email_address)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

This way you don't have to throw email=email_address in there and you get the correct value for LAST_INSERT_ID() if the statement updates.

Source: MySQL Docs: 12.2.5.3