Inserting if not exist or Updating if exist PHP

user_13338535 0
Viewed 133

I want to insert if in the table is not same row with values, but if there is row with the same values I want to only update one that will add +1 to the current value. I have current code, but it doesn't seem to update values in row that exists.

INSERT INTO raport(id, wykonawca, tytul, czas_trwania, powtorzenia) 
        VALUES('','$wykonawca2','$tytul2','$czas_trwania2', '$powtorzenia2') 
        ON DUPLICATE KEY UPDATE wykonawca='$wykonawca2', tytul='$tytul2', czas_trwania='$czas_trwania2', 
        powtorzenia='$powtorzenia2'+1
mysql php
2 Answers

Ensure your table has a column declared as UNIQUE or PRIMARY KEY and is not an Auto-increment column

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

MySQL Reference

EDIT : Your id column is empty you need to pass a value

user_129026490

The actual answer to your initial question as to why the rows do not update is because you are not passing a value for the PRIMARY KEY - this is your AUTO-INCREMENT id column.

Every time you pass :

VALUES('','$wykonawca2','$tytul2','$czas_trwania2', '$powtorzenia2')

this means that your id column is blank, so there is no duplicate just a new row. If you want to have an ON DUPLICATE KEY UPDATE you'll need to pass in the id.

For some more info about ways to handle multiple indexes and DUPLICATE UPDATE check this question out: MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

Also, you should read this and action it as soon as possible, you shouldn't be passing variables straight into sql - it's hugely outdated and very unsafe:

How can prepared statements protect from SQL injection attacks?

user_59224410
Related