如何根据以前的字段是否被占用来更新记录？ [关闭]

Is there something wrong with my syntax its not executing in my php script? I am not updating all the records i am trying to update where invoice_no is equal to the '$id' from another form but only if pp1_dt, pp1_amt, pp1_ref is empty else move on to pp2_dt,pp2_amt,pp2_ref and so on to 5.

$i=1;

while($i <= 5) {
    $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)"; 

    if($db->exec($pp_sql)) {
        $p_num = $i; 
    }
    else {
        $i++;  
    }
}

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.

Shenir, if you want to update any record than you should try to use UPDATE instead of INSERT. if you want INSERT & UPDATE with same query than use key for duplicate records.

I suggest you should read this article http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html