0

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.

Eric
  • 91
  • 2
  • 12
  • 1
    Why are you using `LAST_INSERT_ID()` as the value for `id`? That's usually used when you're inserting a foreign key after creating the row in the master table. – Barmar Aug 24 '17 at 17:08
  • 1
    Normally you leave the `id` field out of inserts like this. You need some other columns to be a unique key. – Barmar Aug 24 '17 at 17:09
  • @Barmar I was unsure of what other value to use there. I thought about passing an array of the values in the 'id' column to the page then when the user clicks 'Save' pass the array back with a value added on for the new item, but I could never get it to work. I was unsure of what to push onto the array. – Eric Aug 24 '17 at 17:10
  • **WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because user data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. **NEVER** put `$_POST`, `$_GET` or any user data directly in your query. – tadman Aug 24 '17 at 17:15
  • At some point you're stomping so much data you might as well use `REPLACE INTO` instead. – tadman Aug 24 '17 at 17:15
  • @Barmar it seems like I might just have to make a dummy column then to serve as a unique key since all the other columns can contain the same values. Would it be possible to make the 'club' column unique but still have multiple entries per club? For instance, club 2 could have several entries as listed above, but not duplicates. Club 3 could have the same entries as club 2, but again no duplicates. – Eric Aug 24 '17 at 17:16
  • Also keep in mind `LAST_INSERT_ID()` changes each time you insert something in a table with an `AUTO_INCREMENT` column so using this inside a loop here could cause complete chaos. – tadman Aug 24 '17 at 17:16
  • @tadman Yes, prepared statements should be used and will updated once I can get the query to function as intended. I tried the REPLACE clause, but ran into the same issue of duplicate entry at the end. – Eric Aug 24 '17 at 17:17
  • 1
    @Eric I may not be able to solve this but if you don't want duplicated values, maybe giving the said column(s) a UNIQUE constraint could be an option and handle on errors. I might not have grasped the question though. You could also check to see if a row exists and handle on that instead. I'm thinking out loud here of course. – Funk Forty Niner Aug 24 '17 at 17:26
  • If you don't have some other unique column in the table, you need some method to tell if the user is creating a new item or updating an existing item. If he's editing an existing item, the form should have an ID input (probably as a hidden input), while this would be omitted for creating new items. You can check for this, and use either `UPDATE` or `INSERT` depending on whether it's supplied. – Barmar Aug 24 '17 at 18:48

0 Answers0