0

I am having some issues with the query below. I want it to run the UPDATE query if theres already a row for the user in the database. I expect for it to insert a row with the values, or update a row with the values just as it says in the query.

I am getting the following error:

syntax error or access violation: 1064 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 'SET balance='40', xplevel='36' WHERE username='nibblenews' AND server='Factions'' at line 1'

$stmt = $db->prepare('INSERT INTO stats (balance, xplevel, username, server) VALUES (:balance, :xp, :username, :server) ON DUPLICATE KEY UPDATE stats SET balance=:balance2, xplevel=:xp2 WHERE username=:username2 AND server=:server2');
$stmt->bindParam(':balance', $balance, PDO::PARAM_STR);
$stmt->bindParam(':balance2', $balance, PDO::PARAM_STR);
$stmt->bindParam(':xp', $xp, PDO::PARAM_STR);
$stmt->bindParam(':xp2', $xp, PDO::PARAM_STR);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':username2', $username, PDO::PARAM_STR);
$stmt->bindParam(':server', $server, PDO::PARAM_STR);
$stmt->bindParam(':server2', $server, PDO::PARAM_STR);
$stmt->execute();
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
user3205106
  • 33
  • 5
  • Whate are 'some issues'? What do you expect and what are you getting? – Pavel S. Jan 19 '14 at 01:01
  • I expect for it to insert a row with the values, or update a row with the values just as it says in the query. I am getting `yntax error or access violation: 1064 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 'SET balance='40', xplevel='36' WHERE username='nibblenews' AND server='Factions'' at line 1'` – user3205106 Jan 19 '14 at 01:02
  • @sonam that is the point of ON DUPLICATE KEY UPDATE, it does the select to check the record exists or not for you and takes the appropriate actions (either insert, or update existing) –  Jan 19 '14 at 01:03
  • @sonam That's not exactly true, MySQL supports doing it in one query: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html – Pavel S. Jan 19 '14 at 01:03

1 Answers1

1

You cannot use an explicit WHERE statement in INSERT ... ON DUPLICATE KEY UPDATE queries. As the MySQL docs say:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

In other words: the columns you specified as UNIQUE or PRIMARY KEY are used to check whether the row already exists. If so, that row is updated.

Furthermore stats SET in your query (after ON DUPLICATE KEY UPDATE) is invalid. You should change your query to something like this:

INSERT INTO stats (balance, xplevel, username, server)
VALUES (:balance, :xp, :username, :server)
ON DUPLICATE KEY UPDATE balance=:balance2, xplevel=:xp2;
Jonathan
  • 6,572
  • 1
  • 30
  • 46
  • Is that update query going to update the row with the username or what? – user3205106 Jan 19 '14 at 01:06
  • @user3205106 If you create a `UNIQUE` index for the `username` column in your table, yes. – Jonathan Jan 19 '14 at 01:06
  • Theres an ID on the table. But I still dont understand how it will find that out. – user3205106 Jan 19 '14 at 01:08
  • @user3205106 Then you should still add the `UNIQUE` index. Such an index makes sure that each row in the table should have a different value for that column (username). That way, MySQL knows when a duplicate row would be created (i.e., when the username is already in use). You can create the index by running the following query on your database (probably in phpMyAdmin or whatever interface/tool you're using): `CREATE UNIQUE INDEX stats_username_unique ON stats(username);` – Jonathan Jan 19 '14 at 01:14
  • I've been trying to help you find a solution and even tried explaining every step. However, you crossed a line in your previous (now deleted) comment by saying "I also think your a warn out dry skank". Good luck with finding a solution and have a good day. – Jonathan Jan 19 '14 at 01:21
  • Sorry dude. I am just so pissed off at this issue im just a little on edge. I understand you dont want to help me anymore but please accept my deepest apologies. – user3205106 Jan 19 '14 at 03:04
  • Thank you. I'd recommend to read something about [unique indexes](http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql). – Jonathan Jan 19 '14 at 13:23