0

I need to make on duplicate key update query, looks there is something missing in my query.

This query works fine with # in values

$insertqry=qa_db_query_sub("INSERT INTO test_table (userid,title, price) VALUES (#,#,#)", $userid, $title, $price);

I need to add ON DUPLICATE KEY UPDATE.

This query works fine with on duplicate update but I need to add # in values to make it SQL injection safe.

$insertqry = qa_db_query_sub("INSERT INTO test_table (userid,title,price) VALUES ('$userid','$title','$price') ON DUPLICATE KEY UPDATE  title='$title', price='$price'");

I am trying this with # in values and does not seem to work.

$insertqry = qa_db_query_sub("INSERT INTO test_table (userid,title,price) VALUES (#,#,#)",$userid, $title, $price); ON DUPLICATE KEY UPDATE  title=#, price=#", $title, $price);

Here is a reference on update DB queries to match format. https://github.com/q2a/question2answer/blob/dev/qa-include/db/post-update.php#L126

Dharman
  • 30,962
  • 25
  • 85
  • 135
site123
  • 41
  • 6
  • `$insertqry = qa_db_query_sub("INSERT INTO test_table (userid,title,price) VALUES (#,#,#) ON DUPLICATE KEY UPDATE title=#, price=#",$userid, $title, $price, $title, $price);` – Nick Apr 14 '20 at 03:36
  • Nick ! I tried this query. I am getting this error - [14-Apr-2020 00:01:17 America/New_York] PHP Question2Answer MySQL query 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 '' at line 1 - Query: INSERT INTO.... Looks like there is typo with " – site123 Apr 14 '20 at 04:04
  • I don't know how you're getting a `"` in the query, there aren't any in the code I wrote other than the ones which delimit the string. – Nick Apr 14 '20 at 04:30
  • Thanks for your reply. My bad. I did not notice that i needed to put $title and $price two times. Now it worked fine without error. Thank you :) – site123 Apr 14 '20 at 04:40
  • Nick ! Your answer is best as well. :) – site123 Apr 15 '20 at 03:11

1 Answers1

0

In your case i guess userid is a primary key or unique key... just try 

$insertqry=qa_db_query_sub("REPLACE INTO test_table (userid,title, price) VALUES (#,#,#)", $userid, $title, $price);

replace will automatically delete first record and insert new if unique key or primary key already exists

EDIT: if you still want to use ON DUPLICATE KEY try..

$insertqry=qa_db_query_sub("INSERT INTO test_table (userid,title, price) VALUES (#,#,#) ON DUPLICATE KEY UPDATE userid= VALUES(userid), title= VALUES(title), price=VALUES(price)", $userid, $title, $price)
Shubham Sakharkar
  • 105
  • 1
  • 9