PHP/MYSQL INSERT ON DUPLICATE KEY fail

Question

I'm just tinkering with a simple timesheet script and if I do a simple insert into the db all is well. I can update the entry no problem either, my issue is that I want to update AND keep the information (which was working fine with the CONCAT I was using) but as soon as I use the ON DUPLICATE KEY UPDATE it breaks.

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')";
//ON DUPLICATE KEY UPDATE
//location=VALUES(location),
//description=VALUES(description),
//timein=VALUES(timein),
//timeout=VALUES(timeout),
//timespent=VALUES(timespent)
//WHERE employee=$employee";
echo $sql;echo "<br>";
mysql_query($sql)or die(mysql_error());

If I uncomment the duplicate key I get a "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE employee='Eric'' at line 9" error. Only thing I can think of is I'm doing this on a totally empty table but I figured the INSERT would have fixed that up. All my $variables are clean (no PDO, gotta learn that too) just real_escape_string

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')
ON DUPLICATE KEY UPDATE
location=VALUES(location),
description=VALUES(description),
timein=VALUES(timein),
timeout=VALUES(timeout),
timespent=VALUES(timespent)
WHERE employee='$employee'";
echo $sql;echo "<br>";
mysql_query($sql)or die(mysql_error());

I'm running this on 5.5 through Webmatrix for testing then I upload to my webhost when I'm happy. — E Martin Laing, Apr 10 '13 at 17:43
The reason I have that is because for each employee on each day I want to be able to update all the information based on WHERE employee='$employee' AND workdate=CURDATE() as I just want on table entry per person per day. — E Martin Laing, Apr 10 '13 at 17:44
`ON DUPLICATE KEY UPDATE` will only ever update the single record for which the keys conflict. You *cannot* have a `WHERE` condition in it, period. — Sammitch, Apr 10 '13 at 17:46
well then, my bad. After all my searching it was such a simple answer. Thank you Sammitch. — E Martin Laing, Apr 10 '13 at 17:47

score 1 · Answer 1 · edited May 23 '17 at 10:25

According to the documentation for INSERT ... ON DUPLICATE KEY UPDATE, there should not be a WHERE statement at the end of your query:

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')
ON DUPLICATE KEY UPDATE
location=VALUES(location),
description=VALUES(description),
timein=VALUES(timein),
timeout=VALUES(timeout),
timespent=VALUES(timespent)";

Notes:

mysql_* functions are now deprecated. Read http://fr.php.net/manual/en/mysqlinfo.api.choosing.php for more information.

Recommended API It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future. A detailed feature comparison matrix is provided below. The overall performance of all three extensions is considered to be about the same. Although the performance of the extension contributes only a fraction of the total run time of a PHP web request. Often, the impact is as low as 0.1%.

if you have not already done so, you must properly escape the text variables before using them in your query:
```
$employee = mysql_real_escape_string($employee);
// and so on with the other variables
```

Read How does this SQL injection work? and Why shouldn't I use mysql_* functions in PHP? to learn more on these topics.

score 0 · Answer 2 · edited Apr 10 '13 at 18:21

0

Don't use a WHERE clause with ON DUPLICATE KEY UPDATE. The WHERE is implied by the duplicate key.

edited Apr 10 '13 at 18:21

Cody Guldner

2,888
1
25
36

answered Apr 10 '13 at 17:43

Scott Saunders

29,840
14
57
64

score 0 · Answer 3 · answered Apr 10 '13 at 18:07

eg:

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

UPDATE table SET c=c+1 WHERE a=1;

if your first column i.e here employee is unique that would be considered for where clause internally

WHERE employee='$employee'

if it wasn't unique it will look for workdate, ...

INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')
ON DUPLICATE KEY UPDATE
[
//columns to be update
location=VALUES(location),
description=VALUES(description),
timein=VALUES(timein),
timeout=VALUES(timeout),
timespent=VALUES(timespent)
]

score -1 · Answer 4 · answered Apr 10 '13 at 17:34

-1

Put the employee in tics. If it is a string it has to have quotes around it. Also put curly braces around the variable.

answered Apr 10 '13 at 17:34

Christoph Grimmer

4,210
4
40
64

sorry, it does have ticks '$employee' I just had them removed because I wanted to see if it made a difference. I've tried with `'" doesn't fix it :( – E Martin Laing Apr 10 '13 at 17:38

PHP/MYSQL INSERT ON DUPLICATE KEY fail

4 Answers4