[Solved] How to update a record based on if previous fields are occupied? [closed]

Question

you can’t use your counter variable inside your string, you need to concatenate it

$pp_sql =  "UPDATE Invoices SET pp" . $i . "_dt="$pp1_dt", pp" . $i . "_amt="$pp1_amt", pp" . $i . "_ref="$pp1_ref" where invoice_no=" . $id . " AND (coalesce(pp" . $i . "_dt, pp" . $i . "_amt, pp" . $i . "_ref) is null)";

the way you have it written now, it’s trying to find columns in your table called pp$i rather than pp1, pp2, etc, which don’t exist.

Accepted Answer

you can’t use your counter variable inside your string, you need to concatenate it

$pp_sql =  "UPDATE Invoices SET pp" . $i . "_dt="$pp1_dt", pp" . $i . "_amt="$pp1_amt", pp" . $i . "_ref="$pp1_ref" where invoice_no=" . $id . " AND (coalesce(pp" . $i . "_dt, pp" . $i . "_amt, pp" . $i . "_ref) is null)";

the way you have it written now, it’s trying to find columns in your table called pp$i rather than pp1, pp2, etc, which don’t exist.