I want to run an update like such in PHP
// pseudocode
UPDATE invoice SET due_date=? WHERE invoice_id=? AND creater_id=?;
IF AFFECTED_ROWS == 1 THEN
UPDATE invoice_item SET price=? WHERE invoice_id=?
For added security, I appended creater_id to ensure that the code only updates if the logged in user is the invoice creator, otherwise, the system will not update.
I originally intended to check this condition using AFFECTED_ROWS. But eventually after much frustration, I realise AFFECTED_ROWS return 0 if all the new values are the same as the old values. This means that even if I have different values for the invoice_item, they will not be updated.
Other than doing a SELECT before the UPDATE, is there SQL query or PHP functions that will tell me if the UPDATE matched any row, so that I can proceeed to UPDATE invoice_item accordingly?
You can use ROW_COUNT() and if you read that it explains that when connecting to the DB you can specify the CLIENT_FOUND_ROWS flag which will give the number of rows found for the update, regardless of if they have the same value of what you're updating with.
Hope this helps.