CODEWITHSUNDEEP
phpmysql
alex
alex

Reputation: 490567

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

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.

Upvotes: 16

Views: 23692

Answers (7)

José Leal
José Leal

Reputation: 8109

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..

Upvotes: 1

mttmllns
mttmllns

Reputation: 1740

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

Upvotes: 3

Peter Howe
Peter Howe

Reputation: 1403

MySQL offers REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Upvotes: 1

Todd
Todd

Reputation: 5117

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.

Upvotes: 33

Gene
Gene

Reputation: 210

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.

Upvotes: 6

victoriah
victoriah

Reputation: 1394

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.

Upvotes: 21

Feet
Feet

Reputation: 2597

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

Upvotes: 0

Related Questions