check duplicate before insert

Posted on 2006-04-20
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

    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

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

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now