check duplicate before insert

Posted on 2006-04-20
Medium Priority
Last Modified: 2012-05-05
given table

      table Directory
Id            auto increment
Name       varchar(10)
Hp            varchar(12)

I need to insert around few hundreds of records in this table. But I had to make sure
'Hp' which doesn't duplicate. It means I need to check if the record exist in table which same with the record
that I want to insert. Any suggestion? Create indexes would slow insertion query. I am code in php,please help.
Question by:MNCW2
LVL 22

Accepted Solution

Ivo Stoykov earned 1000 total points
ID: 16505044
Hello MNCW2,

select count(*) from Directory where Hp = 'string_youre_checking_for';

if it returns > 0 there is a record.

better set up a constraint...


LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 1000 total points
ID: 16505461
Yes. A unique constraint would be the better way to go.

Then, when you insert the row, you need to check the result of mysql_query() to see if it is false.

If it is, then it was a duplicate.

The issue with seeing if it exists BEFORE trying to insert it is in a fast, multi user, multi threaded environment, your code will be too slow.

So, don't bother testing. Simply prevent the duplicate within the DB server/table design itself.

That way, your code CAN NEVER introduce a duplicate. The duplication protection becomes the responsibility of the server. Without it, ALL programs writing to the DB would have to do the check. It would only take 1 to get it wrong!

Adding the index is fine if you are going to be reading the data and filtering/ordering by that column. In fact I would accept the overhead as the unique constraint will actually be faster to test with the index. The overhead of the insert is minimal. Even for 10s of thousands of rows compared to having to do a select (of any sort) to see if it exists and still not having the guarantee that it doesn't when the insert command arrives.

Expert Comment

ID: 16505513
Change HP to UNIQUE in the database structure, and do @mysql_query, as duplicates will throw an error.


Use REPLACE instead of INSERT, assuming 'name' will be the same for any matching HP value

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question