Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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

0
rcowen00
Asked:
rcowen00
  • 6
  • 2
  • 2
  • +1
1 Solution
 
Juan OcasioCommented:
Looks like you're missing an '

WHEN $chargeFee' == NULL THEN NULL

should be

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

WHEN $responseReceived == NULL THEN NULL
0
 
rcowen00Author Commented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Juan OcasioCommented:
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;

0
 
Juan OcasioCommented:
So based on that it should read:

orderNumber =
( CASE
   WHEN NULL THEN NULL
   ELSE '$orderNumber' END),
...
0
 
Juan OcasioCommented:
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;),
...
0
 
rcowen00Author Commented:
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.
0
 
Juan OcasioCommented:
Try it the way I described above.  
0
 
lexlythiusCommented:
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

0
 
lexlythiusCommented:
I believe you are also confused with what belongs in SQL and what belongs in PHP.

Equality comparison is tested with == in PHP
var_dump($myVar == 'blah');

Open in new window


but with = in SQL:
SELECT myField = 'blah';

Open in new window


What you're building in PHP is a query string to send over to the DB. So, PHP string concatenation rules apply. Meaning, this code you tried
WHEN '$orderNumber' IS NULL

Open in new window

can never be true, since you are comparing a string (empty or not) against NULL.
This PHP code:
<?php
$orderNumber = null;
echo "WHEN '$orderNumber' IS NULL";

Open in new window

yields
WHEN '' IS NULL

Open in new window


That SQL query string is sent to the DB. And in SQL, comparing NULL against anything (even against itself) returns ... NULL:
mysql> SELECT NULL=NULL;
+-----------+
| NULL=NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

Open in new window


Which is why NULLity must be tested using IS operator, as in
mysql> select NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Open in new window

0
 
crazedsanityCommented:
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).
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now