Why On Duplicate key update not working in pdo

PHP
1 
$STUDENT_ID = $_POST['STUDENT_ID'];
$FEE_TYPE = $_POST['FEE_TYPE'];
$AMOUNT = $_POST['AMOUNT'];
$TOTAL = $_POST['TOTAL'];
$PAID_DT = $_POST['PAID_DT'];
$PAID_AMT = $_POST['PAID_AMT'];
 for($i=0; $i<$_POST['FEE_TYPE']; $i++){

       $stmt = $conn->prepare("INSERT INTO fee (STUDENT_ID,FEE_TYPE, AMOUNT,TOTAL,PAID_DT,PAID_AMT) VALUES (:STUDENT_ID,:FEE_TYPE, :AMOUNT,:TOTAL,:PAID_DT,:PAID_AMT) ON DUPLICATE KEY UPDATE STUDENT_ID=VALUES(STUDENT_ID), FEE_TYPE=VALUES(FEE_TYPE), AMOUNT=VALUES(AMOUNT), TOTAL=VALUES(TOTAL), PAID_DT=VALUES(PAID_DT), PAID_AMT=VALUES(PAID_AMT)");
       $stmt->execute(array(':STUDENT_ID'=>$STUDENT_ID,':FEE_TYPE'=>$FEE_TYPE[$i], ':AMOUNT'=>$AMOUNT[$i], ':TOTAL'=>$TOTAL[$i], ':PAID_DT'=>$PAID_DT[$i], ':PAID_AMT'=>$PAID_AMT[$i]));
 }
2

When something doesn’t work, it means that there is an error somewhere.
In order to fix an error, you have to read the error message.
In order to read the error message, you have to tell PDO and PHP to provide it for you.
Thus set PDO in exception mode and check the PHP error message.

BTW, prepare is supposed to be outside the loop

1 Like
3

You should be validating the user submitted data, in your case it looks like all the fields are supposed to be numeric so can be simply validated by being typecast as integers

4

I’m confused by your loop - in the query itself, $FEE_TYPE appears to be an array of values that you index by the $i loop variable, yet in the start of the loop you use $i<$_POST[‘FEE_TYPE’] to limit the number of executions. Can you do that, will it automatically return the number of elements without using count()?

But “not working” doesn’t really give us a lot to go on.

5

Your SQL is incorrect - you cannot use VALUES() in the ON DUPLICATE KEY UPDATE part of the query - VALUES() is only allowed in the INSERT INTO part of the query. Have a look at the correct syntax of the INSERT … ON DUPLICATE KEY UPDATE.

6

I had a look at that earlier as I’m not that familiar with sql at all, but it seems to suggest that you can use it as the OP has. It says

“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. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT … UPDATE statements and returns NULL otherwise”

We don’t even know what it’s doing wrong, though.

7

Nice, I didn’t know this usage. Thanks for correction.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.