ON DUPLICATE KEY UPDATE value; inserting same values twice

Question

I am trying to insert some values and if there is a DUPLICATE KEY UPDATE table person. I am getting the insert of values but it is repeating the values instead of doing an update. The input fields are dynamic and values can be added or deleted. But I am trying to not have repeating values. How can insert new values and if there is a duplicate key to then update the fields?

Html/(jquery function not included generates more input fields)

<ul id="pq_entry_1" class="clonedSection">
        <li>
            <input id="person_fname_1" name="person_fname_1" placeholder="Person #1 - First Name" type="text" />
        </li>
        <li>
            <input id="person_lname_1" name="person_lname_1" placeholder="Last Name" type="text" />
        </li>
</ul>
<input type='button' id='btnAdd' value='add another Person' />
<input type='button' id='btnDel' value='delete Delete' />

PHP/Mysql Query

        //Insert or Update Values 
        $f = 1;
        while(isset($_POST['person_fname_' . $f]))
        {

            $person_fname = $_POST['person_fname_' . $f];
            $person_lname = $_POST['person_lname_' . $f];


$query_init3 = "INSERT INTO person (academy_id, first_name, last_name) VALUES (:id,:person_fname,:person_lname) 
        ON DUPLICATE KEY UPDATE academy_id=:id, first_name=:person_fname, last_name=:person_lname";
        $query_prep3 = $db_con->prepare($query_init3);
            $query_prep3->execute(array(
                "id" => $id,
                "person_fname" => $person_fname,
                "person_lname" => $person_lname
            ));

            $f++;
        }

Table after query:

enter image description here

Jason Heo · Accepted Answer · 2013-12-03 03:56:51Z

3

All sort of duplication check SQL

REPLACE INTO
INSERT .. ON DUPLICATE
INSERT IGNORE

works when table has Primary key or UNIQUE index. I think there is no UNIQUE INDEX. (maybe person_id is PK for AUTO_INCREMENT)

so, could you post your person's create statement? If person has not UNIQUE(person_fname, person_lname) you should add it with following sql:

ALTER TABLE person ADD UNIQUE (person_fname, person_lname);

answered Dec 3, 2013 at 3:56

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tadman Over a year ago

That's right. You need to define your duplication conditions with UNIQUE constraints.

Code_Ed_Student Over a year ago

That makes more sense. In this case Yes, person_id is the PK with auto increment.

Collectives™ on Stack Overflow

ON DUPLICATE KEY UPDATE value; inserting same values twice

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related