2

I am currently trying to do quite a simple task. Information is retrieved by "post.php" where it is processed and should be inserted in a database, if the ID (auto increment) doesn't exist, or update the data if the ID exists.

Running the script writes nothing in the database. I have tried to print the query in a page, and copy/paste the SQL into PhpMyAdmin. It returns

#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 'long, description, mobile, type, reported, embed, live, img_url) VALUES ('', 'TE' at line 1

My code is:

$query="INSERT INTO xxy (ID, name, address, lat, long, description, mobile, type, reported, embed, live, img_url) VALUES('$id', '$name', '$address', '$lat', '$lon', '$description', '$mobile', '$type', '$reported', '$embed', '$live', '$target_file') ON DUPLICATE KEY UPDATE
name=VALUES('$name'), address=VALUES('$address'), lat=VALUES('$lat'), long=VALUES('$lon'), description=VALUES('$description'), mobile=VALUES('$mobile'), type=VALUES('$type'), reported=VALUES('$reported'), embed=VALUES('$embed'), live=VALUES('$live'), img_url=VALUES('$img_url')";

Thank You for your help!

UPDATE:

I have also tried backticking reserved words. My current SQL code is still throwing back an error.

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 ''$name'), address=VALUES('$address'), lat=VALUES('$lat'), lon=VALUES('$lon'), de' at line 2

The updated SQL code I am using is: 

 INSERT INTO `xxy` (`ID`, `name`, `address`, `lat`, `lon`,
 `description`, `mobile`, `type`, `reported`, `embed`, `live`,
 `img_url`) VALUES('$id', '$name', '$address', '$lat', '$lon',
 '$description', '$mobile', '$type', '$reported', '$embed', '$live',
 '$target_file') ON DUPLICATE KEY UPDATE     `name`=VALUES('$name'),
 `address`=VALUES('$address'), `lat`=VALUES('$lat'),
 `lon`=VALUES('$lon'), `description`=VALUES('$description'),
 `mobile`=VALUES('$mobile'), `type`=VALUES('$type'),
 `reported`=VALUES('$reported'), `embed`=VALUES('$embed'),
 `live`=VALUES('$live'), `img_url`=VALUES('$img_url')
Vlad Dumitrache
  • 301
  • 4
  • 13
  • you're probably using a character that MySQL doesn't agree with, being an apostrophe. I.e.: `John's Coffee Shop`, am I right on this? If so, MySQL is interpreting that as `John\'s Coffee Shop` causing a syntax error. You will need to escape your data. – Funk Forty Niner Sep 14 '15 at 12:33
  • I have also tried executing the above code directly in PMA. Still gives the same error – Vlad Dumitrache Sep 14 '15 at 12:34
  • `ON DUPLICATE KEY UPDATE name='$name', address='$address'...` Don't need to add `VALUES` – ojovirtual Sep 14 '15 at 12:38
  • Here's hoping all those variables in your query have been properly escaped. – WWW Sep 14 '15 at 12:40

1 Answers1

3

Remove all VALUES key word in ON DUPLICATE KEY UPDATE section

Nitromoon
  • 378
  • 1
  • 2
  • 11
  • why? https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html from the manual `INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);` – Funk Forty Niner Sep 14 '15 at 12:36
  • @Fred-ii- Because the OP is using it wrong. Either he gets rid of every instance of `VALUES()` but leaves what's inside the parenthesis, or he keeps `VALUES()` and changes what's inside to the **name** of the field and not the **value** of the field. – WWW Sep 14 '15 at 12:37
  • @Crontab The manual's a bit confusing then. Ok... fair enough. Let's see what the OP has to say ;-) – Funk Forty Niner Sep 14 '15 at 12:39
  • 1
    Works like a charm! Thanks guys! I owe you! – Vlad Dumitrache Sep 14 '15 at 12:43