ON DUPLICATE KEY UPDATE value; inserting same values twice
mysql, php
Solution
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);
Problem
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: