• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

Update a field with a variable that can be either be a string or a NULL

MS-SQL Server 12

HTML passes a datetime string like "2018-04-25T04:00:00.000Z"  or as a NULL to PHP.

PHP creates a variable for it:
$isReady = array[ "IsReady" ];

Open in new window

If it is a datetime string then I update a table like this:
UPDATE table SET IsReady = '$isReady' WHERE RecordID = 1;

Open in new window

If it is a NULL value then I update a table like this:
UPDATE table SET IsReady = NULL WHERE RecordID = 1;

Open in new window

They both work fine but is there a way to create a single SQL UPDATE statement to handle setting the field if the variable is either a datetime string or is NULL?
0
Joe Weinpert
Asked:
Joe Weinpert
  • 2
1 Solution
 
gr8gonzoConsultantCommented:
Sort of. SQL Server is going to respect whatever values you send to it - it is not going to try to guess what values you want, so you need to ensure that you're sending the desired value in the query. In other words, it's your responsibility to build the query, but you can do something like this:

if($isReady != null)
{
  $query = "UPDATE table SET IsReady = '$isReady' WHERE RecordID = 1;";
}
else
{
  $query = "UPDATE table SET IsReady = NULL WHERE RecordID = 1;";
}

Open in new window


Or even more concise:
$query = "UPDATE table SET IsReady = " . ($isReady ? "'$isReady'" : "NULL") . " WHERE RecordID = 1;";

Open in new window


So you're still building one query, but you're just building it differently based on whether $isReady has a value or not.

Also, since you indicated $isReady is either blank/null or a date/time, I was able to just use a shortcut in the code with "($isReady ? ...." but if you're ever dealing with similar situations where $isReady (or whatever your variable name is in the future situation) could be the number zero (valid) or null, then you'll have to be more explicit in the condition:

$query = "start of the query blah blah SET somevar=" . ($myVar !== null ? $myVar : "NULL") . " rest of the query";

This is because PHP can treat "null", "false", and "0" all as false values in conditions, and "true" and any positive numbers or strings as true values in conditions:
<?php
$myvar = true;
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = 1;
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = "abc";
if($myvar)
  echo "This will run.";
else
  echo "This will not run.";

$myvar = false;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = 0;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = null;
if($myvar)
  echo "This will not run.";
else
  echo "This will run.";

$myvar = 0;
if($myvar !== null)
  echo "This will run.";
else
  echo "This will not run.";

Open in new window


So if you want to check to see if a variable is truly null, then you have to use 3 equals signs:
if($var === null)
or negate it similarly:
if($var !== null)

Or you can also use is_null, too:
if(is_null($var))

I'm just spelling this out so you're being cautious when building queries dynamically by checking the values of your variables.
0
 
Joe WeinpertSr Software EngineerAuthor Commented:
Yes, that is the way I am currently handling it.

I was hoping there would be a way to use the MS-SQL clauses like CASE or IF...ELSE within a single UPDATE command in a manner similar to they way they are used in the SELECT command.
0
 
Julian HansenCommented:
Have you had a look at ALLOW_INVALID_DATES mode?

Alternatively you could try

$query = "UPDATE table SET isReady = IF(LENGTH({$isReady})=0,NULL,{$isReady}) WHERE RecordID = 1";

Open in new window

0
 
gr8gonzoConsultantCommented:
I suppose in theory you could do something like this:

UPDATE table SET IsReady = (CASE WHEN ISNULL(NULLIF('$isReady', '')) THEN NULL ELSE '$isReady' END) WHERE RecordID = 1;

...but that results in a longer query, places unnecessary burden on the database, takes a little bit longer to run compared to just building the query on the client side (PHP), and makes your query less portable.

So CAN you do it? Yes. SHOULD you do it that way? Probably not, but that's your choice.

Personally, whenever I have the choice between making the web server do something versus the database server, I take every opportunity to place load on web servers instead. Web servers are extremely cheap and easily scalable and can easily handle "grunt" work like this. The DB server should be viewed as a precious, limited resource that is often used by many different applications and servers, so if you can spare it from having to do simple tasks, then you're extending the life and usability and performance of that DB server. Especially when you're dealing with a more expensive DB server like SQL server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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