Skip to content
Advertisement

What is correct format for on duplicate key update sql query ? Looks like typo mistake or something missing

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

Advertisement

Answer

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)

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement