SQL update to NULL using a variable

Question

I keep having problems with quotes in relation to a table update. I'm sending a Post with several values from a form, and then update a table with them. For the code to work, I need to wrap keys with backslash ($ColumnaString), and values with single quotes ($ValueString). This works OK. My problem is that occasionally I want to update to NULL (when $value==""). But my present code don't do that. Can somebody spot the problem?

$id_tag=trim($_POST['id']);
foreach($_POST as $key=>$value){
    if ($key!="UpdatePeople"){
        $ColumnaString="`".$key."`";
        $ValueString="'".iconv('UTF-8', 'ISO-8859-1//TRANSLIT', utf8_encode($value))."'";
        if ($key=="In_Date" and $value=="") {$ValueString==NULL;} //Hereis my problem I think
        $link->query("UPDATE MyTable SET ".$ColumnaString."=".$ValueString." WHERE `id`=".$id_tag."");
    }
}

ScaisEdge · Accepted Answer · 2017-02-05 16:55:34Z

1

You could check $id_tag and create a proper part of sql code

$str = ($id_tag ='' ) ? ' is null ' : ' = '.$id_tag;

$link->query("UPDATE MyTable SET ".$ColumnaString." = ".$ValueString." WHERE `id`".str."");

and for $vale

if ($key=="In_Date" and $value=="") { $ValueString = 'NULL' ;} //Hereis my problem I think

edited Feb 5, 2017 at 16:55

answered Feb 5, 2017 at 16:33

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CMArg Over a year ago

Tried it with no results. In order to understand your suggestion: you are asking if $id_tag is empty, in that case you set the variable $str to is nul, else concatenate = and the value of $id_tag, right? If it is this way, I think I should apply something like that to the value associated with $key=="In_Date".

CMArg Over a year ago

Thats it! Single quotes, double quotes, backquotes are going to drive me crazy... Thank a lot!

svyat1s · Accepted Answer · 2017-02-05 16:12:18Z

0

check your database if the columns is defined as NOT NULL

answered Feb 5, 2017 at 16:12

svyat1s

9539 gold badges12 silver badges21 bronze badges

1 Comment

CMArg Over a year ago

DB is ok. Nulls allowed (actually, is full of nulls right now).

Collectives™ on Stack Overflow

SQL update to NULL using a variable

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related