rcowen00
asked on
PHP Case Syntax
Can anyone tell me what is wrong with my CASE syntax. I keep getting the following error.
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 '== NULL) THEN NULL ELSE '' END), cha' at line 4
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 '== NULL) THEN NULL ELSE '' END), cha' at line 4
$editReprocessing= "UPDATE reprocessing SET repAttn='$repAttn', repFax='$repFax', repPhone='$repPhone',
orderNumber =
( CASE
WHEN '$orderNumber' == NULL THEN NULL
ELSE '$orderNumber' END),
chargeFee =
( CASE
WHEN $chargeFee' == NULL THEN NULL
ELSE '$chargeFee' END),
responseRatingKey='$responseRating',
responseReceived=
( CASE
WHEN $responseReceived == NULL THEN NULL
ELSE '$responseReceived' END),
firstNotice =
( CASE
WHEN '$firstNoticeDt' == NULL THEN NULL
ELSE '$firstNoticeDt' END),
repStAddress='$repStAddress', firstNoticeProcessor='$firstNotProc', repStAddress2='$repStAddress2', repCity='$repCity',
repStateKey='$repStateKey',
accountNumber=
( CASE
WHEN '$accountNumber' == NULL THEN NULL
ELSE '$accountNumber' END),
repZip='$repZip', companyName='$companyName'
WHERE reprocessingKey=$updateID";
}
Looks like another one:
WHEN $responseReceived == NULL THEN NULL
WHEN $responseReceived == NULL THEN NULL
ASKER
I made those changes, but I am still getting
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 '== NULL THEN NULL ELSE '' END), char' at line 4
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 '== NULL THEN NULL ELSE '' END), char' at line 4
$editReprocessing= "UPDATE reprocessing SET repAttn='$repAttn', repFax='$repFax', repPhone='$repPhone',
orderNumber =
( CASE
WHEN '$orderNumber' == NULL THEN NULL
ELSE '$orderNumber' END),
chargeFee =
( CASE
WHEN '$chargeFee' == NULL THEN NULL
ELSE '$chargeFee' END),
responseRatingKey='$responseRating',
responseReceived=
( CASE
WHEN '$responseReceived' == NULL THEN NULL
ELSE '$responseReceived' END),
firstNotice =
( CASE
WHEN '$firstNoticeDt' == NULL THEN NULL
ELSE '$firstNoticeDt' END),
repStAddress='$repStAddress', firstNoticeProcessor='$firstNotProc', repStAddress2='$repStAddress2', repCity='$repCity',
repStateKey='$repStateKey',
accountNumber=
( CASE
WHEN '$accountNumber' == NULL THEN NULL
ELSE '$accountNumber' END),
repZip='$repZip', companyName='$companyName'
WHERE reprocessingKey=$updateID";
Here is another example of the syntax:
CASE
WHEN var IS NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
CASE var
WHEN NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
CASE
WHEN var IS NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
CASE var
WHEN NULL THEN SELECT 'Hello';
ELSE SELECT 'world.';
END CASE;
So based on that it should read:
orderNumber =
( CASE
WHEN NULL THEN NULL
ELSE '$orderNumber' END),
...
orderNumber =
( CASE
WHEN NULL THEN NULL
ELSE '$orderNumber' END),
...
and also, you may want to check the smeicolon at the end of END like:
So based on that it should read:
orderNumber =
( CASE
WHEN NULL THEN NULL
ELSE '$orderNumber' END;),
...
So based on that it should read:
orderNumber =
( CASE
WHEN NULL THEN NULL
ELSE '$orderNumber' END;),
...
ASKER
Wouldn't it be CASE
WHEN '$orderNumber' IS NULL THEN SELECT NULL
ELSE SELECT '$orderNumber' END CASE),
If so, it doesn't work I get the same error.
WHEN '$orderNumber' IS NULL THEN SELECT NULL
ELSE SELECT '$orderNumber' END CASE),
If so, it doesn't work I get the same error.
Try it the way I described above.
Avoid using CASE if possible. You will get more readable code and better performance.
Since your values come from PHP, you will be better off building the update completely in PHP.
Finally, you want to escape your input values to avoid SQL injections.
Example implementation:
Since your values come from PHP, you will be better off building the update completely in PHP.
Finally, you want to escape your input values to avoid SQL injections.
Example implementation:
<?php
$repAttn = "Richard O'Hara";
$orderNumber = 12883;
$responseReceived = true;
$chargeFee = 1.27;
$editReprocessing = 'UPDATE reprocessing'
. ' SET repAttn = ' . escapeArg($repAttn)
. ', repFax = ' . escapeArg($repFax)
. ', repPhone = ' . escapeArg($repPhone)
. ', orderNumber = ' . escapeArg($orderNumber)
. ', chargeFee = ' . escapeArg($chargeFee)
. ', responseRatingKey = ' . escapeArg($responseRating)
. ', responseReceived = ' . escapeArg($responseReceived)
. ', firstNotice = ' . escapeArg($firstNoticeDt)
. ', repStAddress = ' . escapeArg($repStAddress)
. ', firstNoticeProcessor = ' . escapeArg($firstNotProc)
. ', repStAddress2 = ' . escapeArg($repStAddress2)
. ', repCity = ' . escapeArg($repCity)
. ', repStateKey = ' . escapeArg($repStateKey)
. ', accountNumber = ' . escapeArg($accountNumber)
. ', repZip = ' . escapeArg($repZip)
. ', companyName = ' . escapeArg($companyName)
. ' WHERE reprocessingKey = ' . escapeArg($updateID);
echo $editReprocessing . "\n";
function escapeArg($value)
{
if (is_int($value)) {
return intval($value);
}
elseif (is_string($value)) {
return "'" . mysql_real_escape_string($value) . "'"; // if you're using mysql--use addslashes if not.
}
elseif (is_bool($value)) {
return ($value == true) ? 1 : 0;
}
elseif (is_float($value)) {
return floatval($value);
}
else {
return 'NULL';
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As lexlythius pointed out, testing NULL in SQL is a different matter. The query needs to be formatted as .." IS NULL". As pointed out, NULL is a different beast, so be careful; the only thing that will actually return true when compared against NULL is NULL (empty strings aren't NULL, they're just a zero-length string.
I would also concur that your SQL should be built entirely PHP, or you can use stored procedures and just call the stored procedure in an SQL statement from PHP. Trying to do complex updates using conditional SQL is difficult to read and very much prone to error (as you've already found out).
I would also concur that your SQL should be built entirely PHP, or you can use stored procedures and just call the stored procedure in an SQL statement from PHP. Trying to do complex updates using conditional SQL is difficult to read and very much prone to error (as you've already found out).
WHEN $chargeFee' == NULL THEN NULL
should be
WHEN '$chargeFee' == NULL THEN NULL