PHP MySQL Insert ... On Duplicate Key Update adding duplicate entry

303 views Asked by At

I am pulling multiple arrays of information from a database and displaying items to the user when they load a page. If the user wants to create a new item they can and clicking a save button will send the information back to the database.

If the user clicks 'Save', I use the ON DUPLICATE KEY UPDATE clause to update the database if any of the previous items have been changed or insert a new item if the user created a new one.

$club = $_POST['club'];
$value_1 = json_decode($_POST['value_1']);
$value_2 = json_decode($_POST['value_2']);
$value_3 = json_decode($_POST['value_3']);
$value_4 = json_decode($_POST['value_4']);
$value_5 = json_decode($_POST['value_5']);
$value_6 = json_decode($_POST['value_6']);

foreach ($value_1 as $index => $item) {

        $sql = "INSERT INTO test_stand_map (id, club, value_1, value_2, value_3, value_4, 
        value_5, value_6) VALUES (LAST_INSERT_ID(), $club, $item, $value_2[$index], 
       '$value_3[$index]', $value_4[$index], $value_5[$index], $value_6[$index]) 
        ON DUPLICATE KEY UPDATE club = VALUES(club), value_1 = VALUES(value_1), 
        value_2 = VALUES(value_2), value_3 = VALUES(value_3), value_4 = VALUES (value_4), 
        value_5 = VALUES(value_5), value_6 = VALUES(value_6)";

        $conn->query($sql);
}

However, if the user creates a new item and clicks save and continues to click save, the last entry will continue to be duplicate every click after the original. This also occurs if the the user clicks save once the page loads (without creating a new item).

For example, here is the database the page grabs information from when loaded

The column 'id' is the primary key with a unique index that auto increments.

enter image description here

Here is the database after the user clicks the save button (without creating a new item)

enter image description here

I feel like the issue is caused by the LAST_INSERT_ID(), however I am not sure what a proper solution would be and would appreciate any help.

0

There are 0 answers