Page 1 of 1

insert subquery

Posted: Wed Feb 18, 2009 6:07 pm
by neridaj
Hello,

I'm trying to do an insert that contains a few subqueries to update some invoice amounts but my query isn't working and I think there might be a better way to do this like maybe a stored procedure or function. I'm sure there is a more elegant way to do this so if you have any ideas please let me know.

Code: Select all

 
function update_invoice($invnum, $paid)
{
  // update existing invoice
  $conn = db_connect();
 
  // add current amount paid to new amount paid and subtract from balance
  if(!$conn->query("UPDATE invoice SET inv_amt_paid = (SELECT inv_amt_paid FROM invoice WHERE inv_number = $invnum) + $paid, inv_amt_balance = (SELECT inv_amt_balance FROM invoice WHERE inv_number = $invnum) - (SELECT inv_amt_paid FROM invoice WHERE inv_number = $invnum) WHERE inv_number = $invnum);"))
    throw new Exception('Invoice could not be updated.');
 
  return true;
}
 
Thanks,

J