insert new record to database without duplicating

[chiprivers]

Is there a simple way when inserting a new record to a database to only execute if it is not duplicating an existing record? The way I would currently do this is to query the database first with the new values and see if I get a match, but is there simpler way?

[pocobueno1388]

Make a primary key in your database and it won't allow duplicate entries...

[trq]

is there simpler way?

 

Nope. That is how it is done.

[chiprivers]

Make a primary key in your database and it won't allow duplicate entries...

 

The primary key is the reference key, this is automatically generated when the new record is created, this would not work for what I am doing because there may be duplicate values in the same column but different instances.

[per1os]

It needs to be a primary key with an auto_increment value as an ID.

 

ie:

 

create table tst (
tstID int(11) NOT NULL auto_increment,
tstName varchar(25) NULL,
PRIMARY KEY(tstID)
);

 

It is usually good to have a unique ID for any DB entry. 3NF form is the way to go.

[hackerkts]

I thought it should be set it to Unique in MySQL to prevent duplicate info?

[per1os]

Maybe, but sometimes MySQL can be un-predictable. This way it is always unique no matter what without any surprises.

 

And an FYI, posting some code WILL help us to help you fix your problem.

[chiprivers]

Maybe I didn't make clear my question because the answers, although much appreciated, don't seem to be answering the question!

 

Let me give a little more detail about what I am doing...

 

I have a table with the following columns:

 

recordID int unsigned not null auto_increment primary key

venue varchar(50)

when datetime

 

The recordID column creates an automatic unique ID that acts as the primary key.  The values in the 'venue' and 'when' columns may be duplicated but not a duplicate record when both values match.

 

ie if I have a record: venue => placex and when => 20070317

it would be acceptable to create: venue => placey and when => 20070317 or venue => placex and when => 20070318

but it would not be acceptable to create another record: venue => placex and when => 20070317

 

So what I am doing at the moment is querying the database for entries using the new values of 'venue' and 'when' to see if a record is returned, if there is a record returned, I don't insert a new record.  If there is no record returned, I insert a new record.

 

My question is, rather than doing the above query, is there an additional command I can put in my MySQL query to prevent the new record duplicating an existing record?

 

The reason I want to do this is that the query will exist in a loop repeating for a large number of entries and I wanted to try and reduce the number of queries to the database to make the process a little more efficient.

[per1os]

What you can do instead, is return all records into an array. When running the loop check the in_array() value and see if there is already a record.

 

The only downside to that is the new inserts won't be checked unless you add those to the array also.