Struggling with this one

[thepreacher]

The following is an SQL statement i am struggling with at the moment

Code

Language: sql (GeSHi-highlighted)
$sql1 = "UPDATE custpayments SET depcardbal = depcardbal -". $totcardsubtotal.", projacctbal = projacctbal - ".$totprojsubtotal.", bankacctbal = bankacctbal - ".$bankacctwithdraw.", transfacctbal = transfacctbal - ".$transfacctwithdraw.", transactionstatus = '2' WHERE paytransacid = '".$paytransacid."'";

I get the following error message;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' bankacctbal = bankacctbal - , transfacctbal = transfacctbal - , transactionstat' at line 1

Thanks for any help you can provide.

[GrandSchtroumpf]

Looks like your variables are empty.
You should make sure you initialize them (set them all to 0) when you declare them.

Or maybe try adding a leading zero to your amounts, but that's a quick and dirty fix...

Code:

$sql1 = "UPDATE custpayments
SET
depcardbal = depcardbal - 0". $totcardsubtotal.",
projacctbal = projacctbal - 0".$totprojsubtotal.",
bankacctbal = bankacctbal - 0".$bankacctwithdraw.",
transfacctbal = transfacctbal - 0".$transfacctwithdraw.",
transactionstatus = '2'
WHERE
paytransacid = '".$paytransacid."'";

(i like my SQL statements on multiple lines)

> $paytransacid
You should always pay for your trance acid ;.

[VGR]

the above is correct.

Moreover, you don't need to play with quotes the way you do, and your SQL phrase is not closed.

IMHO, it should read :

Code:

$sql1 = "UPDATE custpayments SET depcardbal = depcardbal -$totcardsubtotal, projacctbal = projacctbal - $totprojsubtotal, bankacctbal = bankacctbal - $bankacctwithdraw, transfacctbal = transfacctbal - $transfacctwithdraw, transactionstatus = '2' WHERE paytransacid = $paytransacid;";

moreover, your variables are empty. Given they're numeric, they should be initialized explicitly at the start of your program/script (a good habit I inherited from Pascal).
A workaround#1 is to use the 0 trick
A workaround#2 is to add before the query line above :

Code:

$totcardsubtotal=(isset($totcardsubtotal)?(($totcardsubtotal>0)?$totcardsubtotal:0):0); // repeat this line for all variables
$sql1 = "UPDATE custpayments ...

A workaround#3 is to explictly cover the case in your SQL statement, but this is a bit overkill :

Code:

$sql1 = "UPDATE custpayments SET depcardbal = depcardbal - case when $totcardsubtotal is not null then $totcardsubtotal else 0 end,

best regards

[thepreacher]

Thanks a lot for your help but the variables are actually retrieved from $_POST earlier on so if they are empty them something real strange is happening. Will have to look into that.

[VGR]

what I wrote is correct, as I usuammy use the same script for offering the FORM and treat the POSTed data.

In your case, always assign a value="0" to your (numeric) form fields. This way, they'll never be empty... Unless the user cleans them up manually, but this should be covered in the javascript client-side form data validation PRIOR to the actual submit. OR use what I said : $somevar=(isset())? stuff with $_POST['somevar'] in stead of $somevar.

like :

Code:

if isset($_POST['submitbuttonname']) {
  $somevar=(isset($_POST['somevar'])?$_POST['somevar']:0;
  // etc
}
// hear continue with POSTed data processing