MySQL Update if value exists, Insert if not in PHP?

Question

$sql_career = "REPLACE INTO career
         (id, battletag, lastHeroPlayed, lastUpdated, monsters, elites, hardcoreMonsters, barbarian, crusader, demonhunter, monk, witchdoctor, wizard, paragonLevel, paragonLevelHardcore)
         VALUES
         ('', '$battletag', '$lastHeroPlayed', '$lastUpdated', '$monsters', '$elites', '$hardcoreMonsters', '$barbarian', '$crusader', '$demonhunter', '$monk', '$witchdoctor', '$wizard', '$paragonLevel', '$paragonLevelHardcore')";

ID auto increments. battletag is unique.

Everything else changes over time. So I want to replace or update an entry if the battletag already exists without it making a new id. If it doesnt exist I want it to make a new entry letting the id auto increment for that unique battletag.

This works with one problem:

 $sql_career = "
    insert INTO career
      (id, battletag, lastHeroPlayed)
    VALUES
      (NULL, '$battletag', $lastHeroPlayed)
    on duplicate key
      update lastHeroPlayed=$lastHeroPlayed;
 ";

If I, for instance, load in two unique rows, the ID auto increments to 1 and then 2 for each. Then if I load up a row that has a duplicate of the unique key of one of the existing rows (and it then updates as it should) this actually triggers the auto increment. So if I then add in a third unique row, its number will be 4 instead of 3.

How can I fix this?

if id is an auto-increment, then you should be inserting a null to trigger getting a new id. inserting an empty string into an int field is "dirty". — Marc B, Jun 20 '14 at 22:11
What can I say.... and yeah actually I was wondering about that not half an hour ago. I'll assume null is the clean way to do it now. — Paul Duncan, Jun 20 '14 at 22:20

Fluffeh · Accepted Answer · 2014-06-20T23:31:44.167

You want to use the on duplicate key ... update syntax instead of replace into.

Define a unique column (primary or unique index) then check it in your statement like this:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

The benefit of using this over a replace into is that replace into will always delete the data you have already and replace it (sort of as the command name implies) with the data that you are supplying the second time round. An update on... statement however will only update the columns you define in the second part of it - if the duplicate is found - so you can keep information in the columns you want to keep it in.

Basically your command will look something like this (Abbreviated for important columns only)

$sql_career = "
    insert INTO career
        (id, battletag, heroesKilled)
    VALUES
        ($id, '$battletag', $heroesKilled)
    on duplicate key
        update heroesKilled=heroesKilled+1;

";

Again, remember that in your table, you will need to enforce a unique column on battletag - either a primary key or unique index. You can do this once via code or via something like phpMyAdmin if you have that installed.

Edit: Okay, I potentially found a little gem (it's about a third of the way down the page) that might do the trick - never used it myself though, but can you try the following for me?

$sql_career = "
    insert ignore INTO career
        (id, battletag, heroesKilled)
    VALUES
        (null, '$battletag', $heroesKilled)
    on duplicate key
        update heroesKilled=heroesKilled+1;

";

There seems to be collaborating evidence supporting this in this page of the docs as well:

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

With auto increment replace is actually just adding rows. i understand what youre saying though. i will make the changes and see how it fucntions, thanks! — Paul Duncan, Jun 20 '14 at 22:13
Updated OP with auto increment issue. Basically I don't want auto increment to phantom-trigger when duplicate unique keys are found — Paul Duncan, Jun 20 '14 at 23:12
@PaulDuncan See edit. I might have learned a cool trick here myself if it works. Please let me know. — Fluffeh, Jun 20 '14 at 23:32
Unfortunately it still increments. Seems like this is an issue for others in the past with past(?) versions. I'll have to check that info http://stackoverflow.com/questions/9189102/too-many-auto-increments-with-on-duplicate-key-update http://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert — Paul Duncan, Jun 21 '14 at 00:12

MySQL Update if value exists, Insert if not in PHP?

1 Answers1