Getting ON DUPLICATE KEY UPDATE working

Question

I have a form and a database table named reports. I have a date field (primary key) and a textarea named changes to say what's been changed on that date. If the date is the same I want to be able to overwrite the information in the 'changes' column for that date.

My insert command, which works on its own, is as follows:

mysql_query("
  INSERT INTO reports (thedate,changes)
    VALUES ('$_POST[thedate]','$_POST[changes]')
");

I understand that I'll need to use ON DUPLICATE KEY UPDATE after my INSERT but after numerous attempts I cannot get it right. Not only do things no update but it seems to break my insert command so even a new row isn't added to the database.

Apologies if this is a duplicate question. After lots of searching and lots of trying I cannot get it to work.

How do your query look like that you have tested? – Marcus Nov 10 '11 at 12:25 — Marcus, Nov 10 '11 at 12:25

Marcus · Accepted Answer · 2011-11-10T12:53:24.123

3

Have a look att REPLACE.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Note that if you have a foreign key with an action ON DELETE it will be triggered when using REPLACE since it does a delete followed by an insert .

Using ON DUPLICATE KEY it could look like this:

INSERT reports (thedate, changes) VALUES ('$_POST[thedate]', '$_POST[changes]')
  ON DUPLICATE KEY UPDATE changes = '$_POST[changes]'

edited Nov 10 '11 at 12:53

answered Nov 10 '11 at 12:24

Marcus

12,296
5
48
66

Yeah, that's done it! I'll accept as soon as it lets me, thanks – Nov 10 '11 at 12:26
Because the old row is deleted and a new row is created the primary key will change, this can cause the URL to change if you have items.php?itemid=1 which is bad for SEO - to get around this you need to get the PK of the old row and put it into the new row too! – 472084 Nov 10 '11 at 12:35
So, just don't use REPLACE but ON DUPLICATE KEY! – netiul Nov 10 '11 at 12:39
@ScottBrown: Read this: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update , before you use `REPLACE`. – ypercubeᵀᴹ Nov 10 '11 at 12:42
@Jleagle that's not correct. Using REPLACE will update the row with matching key. Sure it's deleted before the new insert, but the value will be the same (even if the column has auto inc) – Marcus Nov 10 '11 at 12:42
1

@Marcus: there are several issues with REPLACE. If the table has auto_incremented Primary Key **and another** Unique index, and the PK is not included in the REPLACE, then the PK will be replaced. – ypercubeᵀᴹ Nov 10 '11 at 12:45
If the table is referenced by Foreign Keys from other tables (especially if they have Cascading Delete property), you'll have some rows deleted (in the other tables!). – ypercubeᵀᴹ Nov 10 '11 at 12:46
@ypercube from OP's example we know that's not the case here; even though it's better to use ON DUPLICATE KEY. – Marcus Nov 10 '11 at 12:47
@ypercube you're correct about the foreign key situation. I'll add a note with that in my answer to clarify that. – Marcus Nov 10 '11 at 12:51

netiul · Answer 2 · 2011-11-10T12:46:12.017

This should work:

mysql_query("INSERT INTO reports (thedate,changes) VALUES ('$_POST[thedate]','$_POST[changes]') ON DUPLICATE KEY UPDATE changes=VALUES(changes)");

Disadvantage of using REPLACE is that it's not standard SQL but a MySQL extension. Beside that, when using auto incremented columns, REPLACE will reinsert with a different value. I won't recommend it to anyone.

Edit: ON DUPLICATE KEY isn't standard SQL either. Sorry!

score 1 · Answer 3 · answered Nov 10 '11 at 12:33

This is the plain SQL query:

INSERT INTO reports (thedate, changes)
VALUES ('2011-11-10', 'Lorem ipsum')
ON DUPLICATE KEY UPDATE changes=VALUES(changes)

http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

Now, you absolutely need to sit down and try to understand what SQL is and how it interacts with PHP and differs from it. You are using PHP to compose strings that happen to be code from another language called SQL. The way you are doing it, the resulting code can be valid SQL or not, and it'll depend of the arbitrary data sent by any anonymous visitor. In the best case, your script will crash. In the worse case, the visitor will be able to read confidential data or alter your database. Here's the example in the manual page for mysql_query():

// This could be supplied by a user, for example
$firstname = 'fred';
$lastname  = 'fox';

// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
    WHERE firstname='%s' AND lastname='%s'",
    mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname));

// Perform Query
$result = mysql_query($query);

Thanks for the answer, advice and reading material, I'll check it out. In this case, security isn't important (never thought I'd say that) as it's purely an internal, non-web connected system to be accessed and used my me only. — , Nov 10 '11 at 12:59

Getting ON DUPLICATE KEY UPDATE working

3 Answers3