0

This is the working snippet:

query("INSERT INTO portfolio (id, symbol, shares) VALUES(?, ?, ?) 
       ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)", 
       $_SESSION['id'], $_POST["symbol"], $_POST["shares"]);

Now, it's not obvious to me why + VALUES(shares) is interpreting $_POST["shares"] correctly?

This was my initial (not working) code:

query("INSERT INTO portfolio (id, symbol, shares) VALUES(?, ?, ?) 
       ON DUPLICATE KEY UPDATE shares = shares + VALUES(?)", 
       $_SESSION['id'], $_POST["symbol"], $_POST["shares"], $_POST["shares"]);

Here I'm calling the variable $_POST["shares"] a second time in here: + VALUES(?)

Can someone please clarify my confusion?

3

According to the docs:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

in other words, using VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause just refers the the value of col_name in the INSERT clause.

and sense you passed $_POST['shares'] as the value of the column shares in the INSERT clause, VALUES(shares) is gonna refer to $_POST['shares'] as well in the ON DUPLICATE KEY UPDATE clause.

for the second query to work, you shouldn't use VALUES(?) or VALUES($_POST['shares']) in the ON DUPLICATE KEY UPDATE clause because the VALUES list is used to specify VALUES for specified columns, or as in case of the ON DUPLICATE KEY UPDATE clause, to get the value of a specified column that would be inserted.

what you need here is a numeric value (e.g., the value of $_POST['shares']). so you could have done something like

query('INSERT INTO portfolio (id, symbol, shares) VALUES(?, ?, ?)' . 
    'ON DUPLICATE KEY UPDATE shares = shares + ?',
    $_SESSION['id'], $_POST['symbol'], $_POST['shares'], $_POST['shares']);

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .