Inserting if not exist or Updating if exist PHP

Question

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

Does anything happen? Are there any error messages? `powtorzenia='$powtorzenia2'+1` should probably be `powtorzenia=powtorzenia+1` How about adding curly braces like: `wykonawca='{$wykonawca2}'` or `wykonawca="{$wykonawca2}"`. You should also define the `id` in your `INSERT INTO` section or there isn't any `id` to be updated. (You check the similarity using the `id`, not other values like wykonawca or tytul.) — ZZ-bb, Oct 21 '20 at 12:52
I can't see anything obviously wrong with the sql (apart from the massive sql injection issues). Which suggests you're not passing the data you think you are. You'd need to do some debugging and update the question with your findings and, probably, a little bit more code such was where the query is called and what data it has/where it gets it from — imposterSyndrome, Oct 21 '20 at 12:53
also, are you sure that that's your key (as in, the database key not just the piece of information you want to check against) because one normally wouldn't change the key that's kind of the point of relations - if `powtorzenia` is the key, and then you update it then you'd have to update every reference to it in every other table. I suspect your table auto-increments the id? And i suspect that's the actual key which is never duplicated as you do an insert each time. but i could be wrong — imposterSyndrome, Oct 21 '20 at 12:55
@ZZ-bb It adds row but not updating. No errors. I have edited as you mension powtorzenia=powtorzenia+1 Same its adding row not updating. I have done wykonawca='{$wykonawca2}' still same adding row not updating.My id is set as auto increment. — Maciej Gromadzki, Oct 21 '20 at 12:58
Maybe this is relevant. "If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes." https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html. — ZZ-bb, Oct 21 '20 at 13:00
What happens instead? How do you run that code? If you are really using PHP, please be warned that your code is widely open for SQL injection — Nico Haase, Oct 21 '20 at 13:18

MichaelTheDev · Answer 1 · 2020-10-21T13:20:47.890

1

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

edited Oct 21 '20 at 13:20

answered Oct 21 '20 at 12:58

MichaelTheDev

132
8

why should it be 'not an Auto-increment column' – imposterSyndrome Oct 21 '20 at 13:05
@jameson2012 Auto-increment columns automatically fill the specified column with a value when a new record is inserted into the database. (+1 of the value in the last record on the table) – MichaelTheDev Oct 21 '20 at 13:07
I'd like to have auto increment colum. I have add a unique key to powtorzenia. – Maciej Gromadzki Oct 21 '20 at 13:07
yes, i know what auto-increment does, having used it in production for many years but i don't see how that's relevant in this case? – imposterSyndrome Oct 21 '20 at 13:09
When creating your table just add PRIMARY KEY and AUTO_INCREMENT to the column Example : CREATE TABLE users ( id int AUTO_INCREMENT PRIMARY KEY ..... – MichaelTheDev Oct 21 '20 at 13:09
I have edited code as ```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``` It's updating when I set powtorzenia as unique key but when value is 2 and I want to update +1 it will add new row. – Maciej Gromadzki Oct 21 '20 at 13:12
@jameson2012 when you have an auto_increment column, the values in that column can't be the same. increases on every inserted row so there won't be any duplicate. Therefore the ON DUPLICATE KEY won't have any effect – MichaelTheDev Oct 21 '20 at 13:15
exactly - that is the cause of the issue – imposterSyndrome Oct 21 '20 at 13:16

score 0 · Answer 2 · edited Oct 21 '20 at 14:30

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?

Inserting if not exist or Updating if exist PHP

2 Answers2