Prevent duplicate entries in database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

Prevent duplicate entries in database

Post 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 ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This is an SQL related question. Moved to Databases.

WHERE clauses are not allowed in INSERT queries. Make the column(s) UNIQUE.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

INSERT IGNORE is what you're looking for I think :-) (courtesy of DrTom)
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

Post 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?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

correct
HormonX
Forum Commoner
Posts: 50
Joined: Tue Dec 10, 2002 7:43 pm
Location: Toronto

Post by HormonX »

You could also use php to test if that value already exists in your table before doing INSERT query.

Greg
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
Post Reply