Page 1 of 1

Prevent duplicate entries in database

Posted: Fri Feb 09, 2007 11:06 pm
by SmokyBarnable
Is there a way to INSERT INTO a database table but only if it doesn't create a duplicate row. Could I use WHERE?
So if I am inserting an item number but that item number already exists.

Would this prevent duplicate entries?

Code: Select all

$sqlstring = "INSERT INTO `item_details` VALUES $itemnumber WHERE $itemnumber = 0";
or just WHERE 0 ?

Posted: Fri Feb 09, 2007 11:27 pm
by feyd
This is an SQL related question. Moved to Databases.

WHERE clauses are not allowed in INSERT queries. Make the column(s) UNIQUE.

Posted: Sat Feb 10, 2007 1:27 am
by Kieran Huggins
INSERT IGNORE is what you're looking for I think :-) (courtesy of DrTom)

Posted: Wed Feb 14, 2007 2:34 pm
by SmokyBarnable
My small pea brain has trouble grasping UNIQUE. So if I make one column of my table unique, like an order number for example, and my php script trys to insert a row where the order number already exists, then the insert will not happen?

Posted: Wed Feb 14, 2007 2:40 pm
by Luke
correct

Posted: Fri Feb 23, 2007 3:59 pm
by HormonX
You could also use php to test if that value already exists in your table before doing INSERT query.

Greg

Posted: Sat Feb 24, 2007 3:34 am
by mikeq
You should make a primary key/unique key from the column/columns which should be unique.

On trying to insert a 'duplicate' record you will get an error, which you can then handle and perform some other action based on this, such as updating the record rather than trying to insert a new one if appropriate