Link to home
Start Free TrialLog in
Avatar of rcowen00
rcowen00Flag for United States of America

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
$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";

                   }

Open in new window

Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

Looks like you're missing an '

WHEN $chargeFee' == NULL THEN NULL

should be

WHEN '$chargeFee' == NULL THEN NULL
Looks like another one:

WHEN $responseReceived == NULL THEN NULL
Avatar of rcowen00

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
$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";

Open in new window

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;

So based on that it should read:

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;),
...
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.
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:
<?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';
	}
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of lexlythius
lexlythius
Flag of Argentina image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).